CODEWITHSUNDEEP
mysql
Jez
Jez

Reputation: 65

Testing for the absence of child records in MySQL

I have two database tables with a one-to-many relationship. Is it possible to select records from the 'one' table for which no records exist in the 'many' table using a single SQL statement? If so, how?

Thanks in anticipation.

Upvotes: 2

Views: 171

Answers (3)

pwizzle77
pwizzle77

Reputation: 31

You can pull all the records from the master table that do not have any children in the "many" table like so:

SELECT T.* FROM Table1 T WHERE T.Id NOT IN(SELECT DISTINCT FKID FROM Table2)

FKID is the Foreign Key ID that links back to the master table.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

select *
    from table1
    where not exists (select null 
                          from table2 
                          where MatchingColumn = Table1.MatchingColumn)

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65537

Use an OUTER JOIN:

select p.id
from parent p
left outer join child c on c.parent_id = p.id
where c.parent_id is null

Upvotes: 3

Related Questions