0

I have a table called "obj_rels" where I have have fields such as: 

pri_type
pri_type_id
sec_type
sec_type_id

The type represents the table the relationship is in. For example COM for comment, FIL for file, SBM for submission or ENT for entity. I want to know the entity for each object type. For example,

Let's say I have a comment (#4) on a file (#3) uploaded to respond to a submission (#2) for entity (#1). The obj_rels table would have: 

+----------+-------------+----------+-------------+
| pri_type | pri_type_id | sec_type | sec_type_id |
+----------+-------------+----------+-------------+
| COM      | 4           | FIL      | 3           |
| SBM      | 2           | FIL      | 3           |
| SBM      | 2           | ENT      | 1           |
+----------+-------------+----------+-------------+

There is no logic to Primary and Secondary entry, so it needs to look at both sides to find a match. How can I write a query that would dig back into the relationships to find the "ENT" associated with the "COM"?

The one I've written repeats a union query five times, thinking I probably won't have more than 5 levels ever, but it is extremely slow. I only have about 9k records in the table, and it takes over 30 seconds to run the query.

What is best practice for this type of relationship search?

Thanks!

Curtis Fuller
  • 115
  • 1
  • 2
  • 10
  • Possible duplicate of [Find Nodes in SQL Tree Structure](https://stackoverflow.com/questions/14656573/find-nodes-in-sql-tree-structure) – philipxy Feb 11 '18 at 05:00
  • There are zillions of questions about querying arbitrary hierarchy depths in MySQL correctly (store procedures, soon CTEs in 8.0) & incorrectly (read & update a variable in one query, undefined behaviour). – philipxy Feb 11 '18 at 05:03

0 Answers0