Problem

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

+-------------+---------+
| 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

Sql
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

Sql

Alternative, more efficient solution:

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;