Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query to only return parent table if all subquery has multiple conditions true

I want to ONLY select customers who have purchased both products 22 and 36

Customers

Id Email Name
1 [email protected] Jack
2 [email protected] Robin
3 [email protected] Pop

Sales

Id CustId ProdId Amount
1 1 22 100
2 2 22 100
3 2 36 120
4 2 45 150
5 3 36 120
6 3 45 180
Tags:

2

Answers


  1. 0

    You can display the data you want by joining two tables and specifying the desired product IDs. The following expression provides this.

      SELECT c.* FROM Customers AS c LEFT JOIN Sales s ON s.CustId = c.Id WHERE s.ProdId IN (22,36);
    Login or Signup to reply.
  2. 0

    You can use query as follow

    SELECT c.email, c.name
FROM customer c
JOIN sales s ON c.id = s.customer_id
WHERE s.product_id IN (22, 36)
GROUP BY c.email, c.name
HAVING COUNT(DISTINCT s.product_id) = 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search