SQL Query to select parent that has child records with different values in the same column

Question

This database has a one-to-many relationship where the parent table, Customers, is a list of customers and the child table, Orders, is a list of things they have bought. I'm trying to develop a query to select customers who have bought BOTH a television and a couch. The following query is returning 0 results.

SELECT Customer.*
FROM Customer
JOIN Orders
ON Customer.ID = Orders.customerID
WHERE Orders.item = 'television'
AND Orders.item = 'couch';

Replacing the AND with an OR doesn't work because it returns the customers who have bought both or just one of the items. My goal result is just the customers who have bought both.

Answer 1

You need to leverage subqueries (or union) which flavor of sql are you using?

SELECT *
FROM CUSTOMER c
WHERE EXISTS (SELECT 1 FROM ORDERS o WHERE c.id = o.id and type = 'couch')
  and EXISTS (SELECT 1 FROM ORDERS o WHERE c.id = o.id and type = 'tv')