CODEWITHSUNDEEP
mysqlsql
Oeyvind
Oeyvind

Reputation: 357

Select all customers except if they have another product - SQL

I have two tables. One consists of customers and another consists of products they have purchased:

Table customer

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

Table product

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

I would like to get all the customers that bought a Toaster, unless they also bought a breadbox.

So I have tried the following:

SELECT * FROM Customer
JOIN Product
    ON Customer.CustID=Product.CustID
WHERE Product in
    (SELECT 
        Item
    FROM Product
    WHERE (Item = 'Toaster' AND Item != 'Breadbox'));

And:

SELECT * FROM Customer
INNER Join Product
ON Customer.CustID=PRODUCT.CustID
WHERE Product.Item = 'Toaster'
AND Product.Item NOT IN ('Breadbox');

But both gives the same result, which includes Tom, that does already own a breadbox.

How can I make sure that only the customers that own a toaster, but do not own a breadbox gets listed?

Upvotes: 0

Views: 440

Answers (5)

shawnt00
shawnt00

Reputation: 17915

select c.CustID, min(c.Name) as Name
from Customer c inner join Product p
    on p.CustID = c.CustID
where p.Item in ('Toaster', 'Breadbox')
group by c.CustID
having
    max(p.Item) = 'Toaster' and min(p.Item) = 'Toaster'

or

having
        count(case when p.Item = 'Toaster'  then 1 end) > 0
    and count(case when p.Item = 'Breadbox' then 1 end) = 0

For the second case you don't strictly need the where though it probably helps performance on large tables.

Upvotes: 0

Andy
Andy

Reputation: 56

This is my first post so forgive any missteps. There is a "many to one" relationship between the Customer and Product tables. To make the logical restrictions you want to apply, you would either need to aggregate the Product table or join the Product table twice. In effect, you are seeking to collapse the many to one relationship to a "one to one" relationship.

Here are some examples of where the Product table is joined twice.

SELECT DISTINCT
a.Name
FROM
Customer a
JOIN Product b ON a.CustID = b.CustID
LEFT JOIN Product c on a.CustID = c.CustID AND c.Item = 'Breadbox'
WHERE
b.Item = 'Toaster' AND
c.CustID IS NULL

or (slightly less efficient depending on indexes)

SELECT DISTINCT
a.Name
FROM
Customer a
JOIN Product b ON a.CustID = b.CustID
WHERE
b.Item = 'Toaster' AND
NOT EXISTS (SELECT 1 FROM Product c where a.CustID = c.CustID AND c.Item = 'Breadbox')

And, here is an example of where the Product table is joined once - possibly more complicated than you require.

SELECT
a.Name
FROM
Customer a
JOIN
(
    SELECT 
    CustID, 
    SUM(case when Item = 'Toaster' then 1 else 0 end) sum_Toaster,
    SUM(case when Item = 'Breadbox' then 1 else 0 end) sum_Breadbox
    FROM
    Product
    WHERE 
    Item in ('Toaster','Breadbox')
    GROUP BY
    CustID
    HAVING
    SUM(case when Item = 'Toaster' then 1 else 0 end) > 0 AND
    SUM(case when Item = 'Breadbox' then 1 else 0 end) = 0
) b ON a.CustID = b.CustID

Upvotes: 2

Wajih
Wajih

Reputation: 4383

SELECT distinct * 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'
)

Upvotes: 1

Steve Wakeford
Steve Wakeford

Reputation: 331

SELECT * FROM Customer C
LEFT JOIN Product PT ON C.CustID = PT.CustID AND PT.Item = 'Toaster'
LEFT JOIN Product PB ON C.CustID = PB.CustID AND PB.Item = 'Breadbox'
WHERE PT.Item IS NOT NULL AND PB.Item IS NULL

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT c.CustID, c.Name
FROM customer AS c
JOIN product AS p ON c.CustID = p.CustID
GROUP BY c.CustID, c.Name
HAVING SUM(p.Item = 'Toaster') >= 1 AND SUM(p.Item = 'Breadbox') = 0

Upvotes: 1

Related Questions