Select records that do not have a product associated on a second table-CodePudding

I have two tables:

Table PL:

plid	plname
1	Alice
2	John
3	Danielle

And table PLproducts

plid	productIdentifier
1	membership
1	life
1	dental
2	membership
3	membership
3	life
3	auto

I need to find those plid where productIdentifier does not contain "dental"

Expected results:

plid	plname
2	John
3	Danielle

If I Outer Join for PLproducts <> 'dental', I get all the records that do not contain 'dental' but that is not what Im looking for.

I've never found this scenario before. I understand it may be a simple question.

Thank you all.

CodePudding user response：

You're looking for where something does not exist

select * 
from pl
where not exists (
  select * from plProducts p 
    where p.plid = pl.plid and p.productidentifier = 'dental'
);

CodePudding user response：

There are multiple ways to approach this problem. You might be interested in looking at outer apply. It could be a useful approach in more complicated scenarios.

select pl.*
from pl outer apply (
    select count(*) as hasdental from plproducts pp
    where p2.plid = pl.plid and pl.productidentifier = 'dental'
) as oa
where hasdental = 0;