CODEWITHSUNDEEP
mysql
kylex
kylex

Reputation: 14406

Select all rows that have no children

I'm really having a brain-fail on this one.

I have the following table:

id    parentID     name
1     0            Frank
2     1            Sally
3     1            John
4     3            Beth

I'd like a statement that selects only those items that have no children, so in the previous example:

Sally
Beth

Would be the result. Can't seem to figure out a query to do this without creating a recursive function, which I don't want do to if I can avoid.

Upvotes: 14

Views: 6557

Answers (4)

Christopher Gamella
Christopher Gamella

Reputation: 21

SELECT p.id, 
    (SELECT count(c.id) FROM yourtable AS c WHERE c.parent_id=p.id) AS childs
    FROM yourtable AS p 
        WHERE 1
HAVING childs = 0

Upvotes: 0

ryebr3ad
ryebr3ad

Reputation: 1248

select t.name
from that_table t
where t.id not in (select parentID from that_table);

Upvotes: 5

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

select yt.name
    from YourTable yt
    where not exists (select null from YourTable where parentID = yt.id)

Although less efficient (see: Left outer join vs NOT EXISTS), you could also do this with a left join:

select yt1.name
    from YourTable yt1
        left join YourTable yt2
            on yt1.id = yt2.parentID
    where yt2.id is null

Upvotes: 16

Marc B
Marc B

Reputation: 360662

select person.ID, person.name
FROM table AS person
LEFT OUTER JOIN table AS child ON person.ID = child.parentID
WHERE child.parentID IS NULL

or in less efficient form

select person.ID, person.name, count(child.id) AS number_of_children
FROM table AS person
LEFT JOIN table AS child on person.ID = child.parentID
GROUP BY person.id
HAVING number_of_children = 0

Upvotes: 1

Related Questions