How to find products in one group but not the other

Question

I currently have this SQL query:

SELECT
    product.name "Product",
    section.floor "Floor"
FROM
    products
JOIN
    sales
ON
    products.productID = sales.productID
JOIN
    sections
ON
    sales.sectionID = sections.sectionID
WHERE
    sections.floor = 1 // BUT NOT ON FLOOR = 2

My idea is to see which products are sold on the first floor but NOT on the second floor. I manage to get all products sold on the first, but how can I get rid of the products that are also found on the second?

My thought is to get all products sold on both floors, and remove the list of products sold and remove those from the list of all products on the first floor, but I don't know how to do it.

Answer 1

First of all, you have no need for your GROUP BY statement, so you can get rid of that.

If you want to avoid second floor objects, you should select all products from the first floor at first, and add another subquery that filters out products that are also sold on the second floor.

It looks like this:

SELECT p.name, s.floor
FROM products p
JOIN sales sa ON sa.productid = p.productid
JOIN sections s ON s.sectionid = sa.sectionid
WHERE s.floor = 1 
   AND p.productid NOT IN(
      SELECT p.productid
      FROM products p
      JOIN sales sa ON sa.productid = p.productid
      JOIN sections s ON s.sectionid = sa.sectionid
      WHERE s.floor = 2)

Another way to think about the above is this: Give me every product name and floor for all products sold on the first floor where that productid is not one of the productids that is sold on the second floor.