SQL – How to extract the rows that meet certain conditions

I will describe my problem and also show you what I tried to do in order to solve it.

so first I have 3 tables like that:

I want to get the status and the orderNumber from the orders table for all the orders that the productLine include these 3 categories ( Vintage Cars, Classic Cars, Motorcycles)

SELECT o.status, o.orderNumber
FROM orders o
WHERE EXISTS(
         SELECT p.productLine 
         FROM products p 
         WHERE p.productLine  IN ('Vintage Cars', 'Classic Cars', 'Motorcycles'))

the query above is what I tried to do, but it returns me every row.

I think I don’t understand how it possible to check if the productLine includes Vintage Cars and Classic Cars and Motorcycles. thanks!

Answer

Use aggregation:

select o.status, o.orderNumber
from orders o join
     orderdetails od
     on o.ordernumber = od.ordernumber join
     products p
     on od.productcode = p.productcode
where p.productLine in ('Vintage Cars', 'Classic Cars', 'Motorcycles')
group by o.status, o.orderNumber
having count(distinct p.productLine) = 3;

The having clause is checking that all three product lines are present in an order.