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

user_14867749 0
Viewed 108

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.

sql-server sql
3 Answers

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

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

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

One of the methods is using string_agg and then use having to remove the phrases that includes "dental".

select * from PL p1
where p1.plid in
(select p2.plid from PLproducts p2
group by p2.plid 
having STRING_AGG(productIdentifier,';') NOT LIKE N'%dental%')
user_147999810
Related