SQL n:m Inheritance join
- by Nightmares
I want to join a table which contains n:m relationship between groups. (Groups are defined in a separate table).
This table only has entries listing a member_group_id and a parent_group_id.
Given this structure:
id(int) | member_group_id(int) | parent_group_id(int)
The "base" query looks like this:
select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1
join group_member_group as p2
on p2.member_group_id = p1.member_group_id
The "base" query correctly shows all relationships (I checked by doing it manually.)
The problem is when I try to apply a where clause to this query to filter for a specific group as "point of origin" (the first group for which I want all parent groups) it returns only the closest parents. For example like this:
select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1
join group_member_group as p2
on p2.member_group_id = p1.member_group_id
where p1.group_id = 1
Can anyone give a clue how I can fix this? Or a different approach to realize this. (I suppose I could always do this in my C++ source code on the server side but I would have to transfer a entire table which has a high growth potential to the application server.)
UPDATE:
select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1
join group_member_group as p2
on p2.group_id = p1.member_group_id
Typing mistake confirmed. Now I don't get past first level of inheritance period. Thanks at denied for pointing that out.
