1

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.

|improve this question
  • "I am trying to write a mysql query which will return all the bars that have children and are at level 0 (no parent)." You mean that have children and are NOT at level 0? – Evan Carroll Sep 13 '17 at 0:13
  • @EvanCarroll , I mean that have children and are at level 0. – Opster ES Ninja Sep 13 '17 at 0:38
  • That doesn't make any sense... "bar entities with an id of 1 and 2 are at level 0 (no parent) and have children" So why aren't you returning 1, and 2. – Evan Carroll Sep 13 '17 at 0:40
  • 1
    @EvanCarroll , sorry my bad, thanks for your insightful comment. i'll update my question. – Opster ES Ninja Sep 13 '17 at 0:56
4

You just need to add to the where statement to filter for those that are the primary level

SELECT * 
FROM foo AS f1
WHERE id = parent_id
AND id IN (
  SELECT f2.parent_id
  FROM foo AS f2
  WHERE f1.id = f2.parent_id
    AND f2.id != f2.parent_id
);
|improve this answer
  • Thanks for your answer, but i just updated my question, your query will return "that have children and are NOT at level 0". But i want result which have "that have children and are at level 0". – Opster ES Ninja Sep 13 '17 at 0:57
  • I think you want id=parent_id read the comments on the question – Evan Carroll Sep 13 '17 at 0:58
  • @EvanCarroll, I've updated the question. – Opster ES Ninja Sep 13 '17 at 0:59
  • I went ahead and updated this feel free to roll back that update. The OP changed the question. His data and description was inaccurate. – Evan Carroll Sep 13 '17 at 1:06
  • @EvanCarroll , thanks for the answer, but its returning 3 as well. which doesn't have any child. – Opster ES Ninja Sep 13 '17 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.