This is my situation (the real table isn't about people but about paths):
[Table: People] id | name | type | related_to ---------------------------- 1 | | | 0 2 | Nico | Friend | 1 3 | Mika | Wife | 2 4 | Olaf | Child | 3 5 | Pete | Friend | 1 6 | Duke | Friend | 1
So my query is as followed:
SELECT * FROM `people` base JOIN `people` related1 ON base.id = related1.related_to JOIN `people` related2 ON related1.id = related2.related_to JOIN `people` related3 ON related2.id = related3.related_to WHERE related1.name = 'Nico' AND (related2.name = 'Mika' OR related2.type != 'FRIEND') AND (related3.name = 'Duke' OR related3.type != 'FRIEND');
I'm getting a lot of columns back now but I just want a row per related.
Is the only solution to seperate each join into query's and use UNION to get one result? Or is there a faster/cleaner/better way?
This query can be fired alot, is there a solution to make a view for it? Keep in mind that this query is automaticly created so there may be not 3 relations but also 1 or max. 8.
Is there a better term for self-join? Because my google results really sucked while I searched for a solution..
Really thanks for even reading this with my bad grammar! You rock!