CODEWITHSUNDEEP
mysqlsql
Oeyvind
Oeyvind

Reputation: 357

Filter on second left join - SQL

I have three tables. One consists of customers, one consists of products they have purchased and the last one of the returns they have done:

Table customer

CustID, Name
  1,     Tom 
  2,     Lisa 
  3,     Fred

Table product

CustID, Item
  1,     Toaster
  1,     Breadbox
  2,     Toaster
  3,     Toaster

Table Returns

CustID, Date, Reason
  1,     2014, Guarantee
  2,     2013, Guarantee
  2,     2014, Guarantee
  3,     2015, Guarantee

I would like to get all the customers that bought a Toaster, unless they also bought a breadbox, but not if they have returned a product more than once.

So I have tried the following:

SELECT * FROM Customer
 LEFT JOIN Product ON Customer.CustID=Product.CustID
 LEFT JOIN Returns ON Customer.CustID=Returns.CustID
WHERE Item = 'Toaster'
AND Customer.CustID NOT IN (
Select CustID FROM Product Where Item = 'Breadbox'
)

That gives me the ones that have bought a Toaster but not a breadbox. Hence, Lisa and Fred.

But I suspect Lisa to break the products on purpose, so I do not want to include the ones that have returned a product more than once. Hence, what do I add to the statement to only get Freds information?

Upvotes: 0

Views: 55

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You use conditional COUNT

SELECT C.CustID, C.Name
FROM Customer C
JOIN ( SELECT CustID
       FROM Products
       GROUP BY CustID
       HAVING COUNT(CASE WHEN Item = 'Toaster' THEN 1 END) > 1 
          AND COUNT(CASE WHEN item = 'Breadbox' THEN 1 END) = 0 
     ) P   -- Get customer with at least one Toaster and not Breadbox
  ON C.CustID = P.CustID
JOIN ( SELECT CustID
       FROM Returns 
       HAVING COUNT(*) < 2
     ) R   -- Get only customers with less than 2 returns
  ON C.CustID = R.CustID

Upvotes: 0

schlonzo
schlonzo

Reputation: 1406

How about

SELECT * FROM Customer
LEFT JOIN Product ON Customer.CustID=Product.CustID
WHERE Item = 'Toaster'
AND Customer.CustID NOT IN ( 
    Select CustID FROM Product Where Item = 'Breadbox'
)
AND (SELECT COUNT(*) FROM Returns WHERE Customer.CustId = Returns.CustID) <= 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The filter condition goes in the ON clause for all but the first table (in a series of LEFT JOIN:

SELECT *
FROM Customer c LEFT JOIN
     Product p
     ON c.CustID = p.CustID AND p.Item = 'Toaster' LEFT JOIN
     Returns r
     ON c.CustID = r.CustID
WHERE c.CustID NOT IN (Select p.CustID FROM Product p Where p.Item = 'Breadbox');

Conditions on the first table remain in the WHERE clause.

As a note: A table called Product that contains a CustId seems awkward. The table behaves more likes its name should CustomerProducts.

Upvotes: 0

Related Questions