I have a hierarchical table with an id and parent_id column, the parent_id has a foreign key to the id column. Each row can only have one parent, but multiple rows can be tied to the same parent. I wanted to retrieve all the rows that didn't have any children.
I attempted this with the following query
SELECT *
FROM table
WHERE id NOT IN (
SELECT DISTINCT(parent_id)
FROM table
)
This returned 0 rows. If i change the NOT IN to IN it correctly returns the rows that have children (other rows tie to it through their parent_id)
I ended up getting this working:
SELECT *
FROM table
WHERE id NOT IN(
SELECT id
FROM table
WHERE id IN (
SELECT DISTINCT(parent_id)
FROM table
)
)
But I don't understand why the first query didn't work? Can anybody help me to understand what's going on here? Do I not understand how NOT IN should work?
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…