CODEWITHSUNDEEP
mysqlsqlselect
Arnold Roa
Arnold Roa

Reputation: 7718

SQL query that brings all records that doesn't includes a join with a given value

Lets suppose we have

Owners
id

Dogs
id
owner_id
trained (boolean)

I want to get all owners who don't have any trained dog, including those who don't have a dog.

So if one owner has one trained dog, and one dog non trained, that owner should not appear, because already has a trained dog.

I tried with something like this:

SELECT * FROM owners o 
LEFT JOIN dogs d on d.owner_id = o.id
WHERE
  d.trained = false OR d.trained IS NULL.

But this query returns records including owners that have dogs trained, as long as they have a non trained dog.

I wan't to get only:

Upvotes: 1

Views: 37

Answers (2)

Rohan
Rohan

Reputation: 62

SELECT *
FROM   Owners o,Dogs d
WHERE  o.id=d.owner_id
and  d.trained=false;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311418

The not exists operator does exactly that:

SELECT *
FROM   owners o
WHERE  NOT EXISTS (SELECT *
                   FORM   dogs d
                   WHERE  trained = true AND d.owner_id = o.id)

Upvotes: 1

Related Questions