CODEWITHSUNDEEP
sql
was_777
was_777

Reputation: 711

Query to exclude rows in multiple left joins based on a combination of values

I have 3 tables, Customers, Orders and Order Details

Customers

CustomerID  CustomerName
1           Alfreds Futterkiste 
2           Ana Trujillo Emparedados y helados 
3           Antonio Moreno Taquería

Orders

OrderID CustomerID
10308   2
10365   3

and OrderDetails

OrderDetailID   OrderID
162             10308
163             10308
314             10365

I am able to get customers with combination of a particular value of orderId and orderDetailID. Here it is:

SELECT c.CustomerID, o.OrderID, od.OrderDetailID FROM Customers c 
    LEFT JOIN ORDERS o ON c.CustomerID = o.CustomerID
    LEFT JOIN ORDERDetails od ON od.OrderID = o.OrderID
    WHERE o.OrderID = 10308 AND od.OrderDetailID = 162;

Result

CustomerID  OrderID OrderDetailID
2           10308   162

My requirement is opposite, I dont want such customers with the combination of o.OrderID = 10308 AND od.OrderDetailID = 162. I tried this:

SELECT c.CustomerID, o.OrderID, od.OrderDetailID FROM Customers c 
LEFT JOIN ORDERS o ON c.CustomerID = o.CustomerID
LEFT JOIN ORDERDetails od ON od.OrderID = o.OrderID
WHERE od.OrderDetailID IS NULL 
    OR od.OrderDetailID <> 162 
    OR (od.OrderDetailID = 162 AND o.OrderID <> 10308);

This results in

CustomerID  OrderID OrderDetailID
1           null    null
2           10308   163
3           10365   314

The problem here is I don't want the customer with id 2 at all since it already has the combination of o.OrderID = 10308 AND od.OrderDetailID = 162 . Customer with NULL orders is also fine for me.

How can I exclude the customer who is having such a combination?

Upvotes: 0

Views: 162

Answers (1)

Sergey
Sergey

Reputation: 5225

WITH CUSTOMERS (CustomerID , CustomerName)
AS
(
  SELECT 1,'Alfreds Futterkiste' UNION ALL
  SELECT 2,'Ana Trujillo Emparedados y helados ' UNION ALL
  SELECT 3,'Antonio Moreno Taquería'  
),
ORDERS(OrderID,CustomerID) 
AS
(
  SELECT 10308,   2 UNION ALL
  SELECT   10365,   3 
),
ORDER_DETAILS(OrderDetailID,OrderID)
AS
(
 SELECT 162,10308 UNION ALL
 SELECT 163,10308 UNION ALL
 SELECT 314,10365 
)
SELECT c.CustomerID, o.OrderID, od.OrderDetailID FROM Customers c 
LEFT JOIN ORDERS o ON c.CustomerID = o.CustomerID
LEFT JOIN ORDER_DETAILS od ON od.OrderID = o.OrderID
WHERE NOT EXISTS 
(
   SELECT  X.CustomerID
   FROM Customers X
   JOIN ORDERS Y ON X.CustomerID = Y.CustomerID
   JOIN ORDER_DETAILS Z ON Y.OrderID=Z.OrderID
   WHERE X.CustomerID=C.CustomerID
   AND Y.OrderID = 10308 AND Z.OrderDetailID = 162 
)

Could you please try if the above is suitable for you

Upvotes: 1

Related Questions