SQL query for searching on multiple record with AND condition

Question

Can we have the simplest query for the below scenario, (without using multiple query/nested query/ joins) Table and search parameter

basically we tried with Intersection, SELECT ORDER, PRODUCT FROM SALES_ORDER WHERE PRODUCT = 'P0054' INTERSECT SELECT ORDER, PRODUCT FROM SALES_ORDER WHERE PRODUCT = 'P0095';

but we hope there should be a better approach for this scenario. The search condition may contain as many as values with AND condition. Ideally we are trying to fetch in a single query.

Any input/help is much appreciated.

Thanks, Girish.

Answer 1

Assuming there are no repeated rows (order-product is a unique tuple):

select order
from SALES_ORDER
where product in ('P0012','P0054')
group by order
having count(DISTINCT PRODUCT) = 2

Answer 2

Hope the below query may solve your problem. I didn't tested the query. Please try.

SELECT ORDER, PRODUCT 
 FROM SALES_ORDER 
WHERE (PRODUCT = 'P0054' OR  PRODUCT = 'P0095')
 GROUP BY ORDER, PRODUCT
 HAVING COUNT(*) > 1;