CODEWITHSUNDEEP
sql
user391986
user391986

Reputation: 30896

sql list records with no parents

What's the best way to get the list of parent ID that have no child?

CREATE TABLE parent (
    ID PRIMARY KEY AUTO INCREMENT
);

CREATE TABLE child (
    ID PRIMARY KEY AUTO INCREMENT
    ParentID    INT NOT NULL
);

Upvotes: 0

Views: 102

Answers (4)

Naval
Naval

Reputation: 344

Select Parent ID 
from Parent 
where ParentID NOT IN Select distinct ParentID from ChildTable

this would work :)

Upvotes: 1

Glenn
Glenn

Reputation: 9150

Depending on your flavour of sql:

select ID from parent minus select ParentID from child

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This is one way:

SELECT P.ParentID
FROM dbo.Parent P
LEFT JOIN (SELECT DISTINCT ParentId FROM child) C
ON P.ParentId = C.ParentId
WHERE C.ParentID IS NULL

This is another way:

SELECT P.ParentID
FROM dbo.Parent P
WHERE NOT EXISTS (SELECT * FROM dbo.Child WHERE ParentId = P.ParentID)

Upvotes: 4

juergen d
juergen d

Reputation: 204756

select id from parent
where id not in (select distinct parentid from child)

Upvotes: 2

Related Questions