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

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

CodePudding user response：

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'
);