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.