Problem

1
2
3
4
5
6
7

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+-------------+---------+

Write an SQL query that reports the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products present in the Product table.

Method 1 - Using subqueries

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT DISTINCT buyer_id 
FROM Sales 
WHERE
    buyer_id IN (
        SELECT buyer_id
        FROM Sales s
        JOIN Product p ON p.product_id = s.product_id
        WHERE product_name = 'S8') AND
    buyer_id NOT IN (
        SELECT buyer_id
        FROM Sales s
        JOIN Product p ON p.product_id = s.product_id
        WHERE product_name = 'iPhone');

Method 2 - Using having clause

Code

1
2
3
4
5

SELECT buyer_id
FROM Sales s
JOIN Product p ON p.product_id = s.product_id
GROUP BY buyer_id
HAVING SUM(product_name = 'S8') > 0 AND SUM(product_name = 'iPhone') = 0;