How to select parent rows that doesn't have child and child that has parent rows

Question

I have a little bit complicated query.
I have table

tbl_Categories {CategoryID, Name, CategoryId_fk}

and it it a self referenced table. When CategoryId_fk is NULL then that row is parent when there is a value that it is a child. I have problem to select all children (it's where CategoryId_fk is not null) and rows where CategoryId_fk is null and doesn't have children. I tried something but doesn't work:

SELECT a.*
FROM tbl_Categories a
WHERE NOT EXISTS (
    SELECT 1 FROM tbl_Categories b
    WHERE b.CategoryId_fk= a.CategoryId_fk
)

Search StackOverflow for "Recursive CTE" for dozens of examples of how to solve problems like this. edit Toss in "hierarchy" for more specific examples. — Philip Kelley
Umm.. I think I didnt get it right. if you want all children & all parents, wouldnt that be whole table? Can you provide sample data? — Bhrugesh Patel
@BhrugeshPatel - The whole table without parents who have children. — Lieven Keersmaekers

Lieven Keersmaekers Lieven Keersmaekers · Accepted Answer · 2012-02-29T15:23:13

You have matched both b and a on the foreign key.

I might be misinterpreting your question, most of the times users want to find all childs for a given parent, but below query returns what I think you need.

/* All parents without children */
SELECT  a.*
FROM    tbl_Categories a
WHERE   NOT EXISTS (
          SELECT * 
          FROM   tbl_Categories b
          WHERE  b.CategoryId_fk = a.CategoryId
)
/* All children */
UNION ALL
SELECT  a.*
FROM    tbl_Categories a
WHERE   CategoryId_fk IS NOT NULL

How to select parent rows that doesn't have child and child that has parent rows

1 Answers