SQL query with subquery using NOT operator

Question

Newbie here using W3schools demo database to practice SQL queries and I'm stuck on one:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, OrderDetails.ProductID, Products.ProductName FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.ProductID <> 70

I want to see a list of customers who have made an order, but have not ordered product ID 70.

This query still shows the other orders of customer's who have ordered product ID 70. If a customer has ordered it, I don't want to see any of their other orders on this list. Does this scenario call for a subquery using WHERE EXISTS?

Thanks!

Gordon Linoff · Accepted Answer · 2019-06-10 01:03:09Z

1

Use aggregation:

SELECT c.CustomerID, c.CustomerName
FROM Customers c JOIN
     Orders o
     ON c.CustomerID = o.CustomerID JOIN
     OrderDetails od
     ON od.OrderID = o.OrderID
GROUP BY c.CustomerID, c.CustomerName
HAVING SUM(CASE WHEN od.ProductID = 70 THEN 1 ELSE 0 END) = 0;

This is a different question from asking which orders have product 70. That question can be answered just by looking at a row to see if the product is there. However, to determine whether or not product 70 is in any order, you need to look at all of them, which is why aggregation is needed.

answered Jun 10, 2019 at 1:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Obsidian Age · Accepted Answer · 2019-06-10 00:28:50Z

1

It sounds like you're just looking for the NOT keyword:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, OrderDetails.ProductID, Products.ProductName FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID
WHERE NOT OrderDetails.ProductID = 70

answered Jun 10, 2019 at 0:28

Obsidian Age

42.6k11 gold badges54 silver badges76 bronze badges

2 Comments

user330315 Over a year ago

NOT OrderDetails.ProductID = 70 is exactly the same thing as OrderDetails.ProductID <> 70

massaskillz Over a year ago

This yields the same result, which is not what I'm looking for.

score 1 · Accepted Answer · 2019-06-10 01:33:29Z

1

You can indeed use an NOT EXSISTS query for that:

SELECT c.*
FROM customers c
WHERE NOT EXISTS (select *
                  from orders o
                    join orderdetails od on o.orderid = od.orderid
                  where o.customerid = c.customerid
                    and od.productid = 70);

The subquery tests if there is at least one order with productid = 70 for each customer. If no such order exists, NOT EXISTS will result in "true", thus including that customer in the overall result.

As you only want the list of customers, there is no need to join in the orders table into the main query.

edited Jun 10, 2019 at 1:33

answered Jun 10, 2019 at 1:11

user330315

1 Comment

massaskillz Over a year ago

Thanks, this works too, but it shows all customers including ones that have not ordered anything. I edited my question to further clarify that the query is for customers who have made an order.

Collectives™ on Stack Overflow

SQL query with subquery using NOT operator

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related