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.