I am sorry if this is a very basic question, but I have not been able to find an answer anywhere :
Let's say I want to get the products that are sold at both stores a and b, with a single table that associates products and stores. In class, we have been taught this kind of solution :
select product_id
from product
where store = "a"
and product in (
select product_id
from product
where store = "b"
)
This however seems very inefficient to me, especially in a case where a very large table would be used. Is there any way to make a single request that satisfies both conditions, without nesting ?
It feels like there has to be, but being very new to sql I actually have no idea, and I have found this type of thing extremely hard to look up. No matter what I search, I find answers to similar problems that don't answer my question.
CodePudding user response:
I would approach this by first filtering for the required criteria followed by a corresponding distinct count:
select product_id
from product
where store in ('a','b')
group by product_id
having Count(distinct store)=2;
CodePudding user response:
if I understand correctly you can try to use condition aggregate function in HAVING
SELECT product_id
FROM product
GROUP BY product_id
HAVING COUNT(CASE WHEN store = 'a' THEN 1 END) > 0
AND COUNT(CASE WHEN store = 'b' THEN 1 END) > 0