SQL - grabbing rows with two conditions but excluding everything else-CodePudding

Say you have a column called service_id. There are multiple instances of this. (its not unique).

And theirs a another column- product code with a bunch of them.

So we have

service id:   Product Code: 
1             dog
1             cat
1             mouse
2             dog
2             cat
3             mouse

I want to pull only the service id's that have product codes of dog & cat ONLY, but nothing else. so if service id has a mouse also, i don't want to include that.

In this case i would only want to pull service id 2, since it only has dog cat.

How can i write this in SQL?

We want only ids that have cat & mouse but nothing else.

CodePudding user response：

You may use conditional aggregation as the following:

select serviceId from table_name
group by serviceId
having count(case when ProductCode not in ('cat','dog') then 1 end)=0

See a demo.

If you want to ensure that 'serviceId' has a dog AND cat you may try the following:

select serviceId from table_name
group by serviceId
having count(case when ProductCode not in ('cat','dog') then 1 end)=0
       and count(case when ProductCode ='cat' then 1 end)>0
       and count(case when ProductCode ='dog' then 1 end)>0

CodePudding user response：

The way I'd have done it is to SELECT the invalid IDs separately:

SELECT DISTINCT service_id
FROM product
WHERE service_id NOT IN (
  select service_id
  from product
  where product_code not in ('cat', 'dog')
)

and make the second select into a CTE if it's any more complicated than that.

But Ahmed's solution is more flexible and expressive, I think.