CODEWITHSUNDEEP
mysqljoin
user1904273
user1904273

Reputation: 4764

mysql join to exclude missing entries

I have two tables and I want to display all records from the first table where the field that joins them is not present in the second table.

$sql = "SELECT d.*,t.id from `donations` d
JOIN `teams` t
ON d.teamid = t.id
 WHERE t.id IS NULL";

In other words I want to join donations and teams. But I want to retrieve only the records from donations where the team field is not present in the teams table.

The above displays zero records and is not doing the trick.

Thanks for any suggestions.

Upvotes: 0

Views: 54

Answers (2)

Lars Nyström
Lars Nyström

Reputation: 6373

You could use a sub query.

select * from donations
where teamid not in (
    select id from teams
)

That should select all donations which has a teamid which is not present in the teams table.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT d.*,t.id from `donations` d
LEFT OUTER JOIN `teams` t
ON d.teamid = t.id
 WHERE t.id IS NULL

Upvotes: 3

Related Questions