CODEWITHSUNDEEP
sqlsql-servert-sql
Rick
Rick

Reputation: 45

SQL WHERE none of the joined records in the table match certain criteria?

I have one Table

Table1

ID
Cat
Dog
Rabbit

And another Table

Table2

ID Action
Cat Eat
Cat Sleep
Cat Poop
Dog Eat
Dog Sleep
Rabbit Eat

I want to find which animal hasn't slept - but when I join table1 on table2 where ID = ID and filter for when the action <> 'Sleep' I still get a record for Rabbit because Sleep <> Eat.

I want to join table1 on table2 and look at all of the records in the join, and if none of the records = Sleep, then return the ID.

If we were looking for no Sleep I want to return Rabbit, and no Poop to return Dog & Rabbit

Upvotes: 0

Views: 50

Answers (1)

Stu
Stu

Reputation: 32599

It looks like you just need a not exists criteria, try the following

select id
from table1 t1
where not exists (
  select * from table2 t2 
  where t2.id = t1.id and t2.action = 'sleep'
);

Upvotes: 1

Related Questions