I've a table called foo
which stores the child and parent information of some entity called bar
, with level information as well. If there is no parent of bar
, then in the parent_id
column it will store its own id.
id parent_id
1 1
2 2
3 3
4 1
5 2
6 4
So in my example, the bar entities with an id of 1 and 2 are at level 0 (no parent) and have children. The bar with an id of 4 has a child but it also has a parent, so its not at level 0.
I am trying to write a mysql query which will return all the bars that have children and are at level 0 (no parent).
I am able to write the query below, which shows the bars
that have children:
select * from foo where id!=parent_id;
And output of above query is :
id parent_id
4 1
5 2
6 4
But in above query, parent_id 4 is not at level 0 and I need a query that produces the output below:
id parent_id
1 1
2 2
Let me know if you need any additional information from my side.
Edit :- Thanks Evan for pointing out my mistake, actually I want ids "that have children and are at level 0". Also I've updated the correct O/P.