CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlexists
TTCG
TTCG

Reputation: 9113

Get Customers List who have not ordered a particular kind of product

It is in SQL Server 2008 R2.

We have 3 products, Orders and Customers: P1 P2 P3

We'd like to know Customers who ordered P1 and P2 BUT NOT P3. We don't want to get Customers who ordered all 3 kinds of products. I don't know how to write that query. Could you please advise me? Thanks.

We have only 2 tables. Orders and Customers. Products Name are fixed.

Upvotes: 1

Views: 9173

Answers (5)

nareash
nareash

Reputation: 1

select CustomerId
from 
(
   select o.*,
     (case when productid = <product1> then 1 else 0 end) as has_p1,
     (case when productid = <product2> then 1 else 0 end) as has_p2,
     (case when productid = <product3> then 1 else 0 end) as has_p3
   from Orders o
) o
group by CustomerId
having max(has_p1) = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

select CustomerId
from 
(
   select o.*,
     (case when productid = <product1> then 1 else 0 end) as has_p1,
     (case when productid = <product2> then 1 else 0 end) as has_p2,
     (case when productid = <product3> then 1 else 0 end) as has_p3
   from Orders o
) o
group by CustomerId
having max(has_p1) = 1 
  and max(has_p2) = 1 
  and max(has_p3) = 0

Upvotes: 0

MatBailie
MatBailie

Reputation: 86745

Having been forced to guess the schema, and assume that a customer can order the same product multiple times, you can use something like this...

WITH
  Products (
    productID,
    inclusive
  )
AS
(
            SELECT 'P1', 1
  UNION ALL SELECT 'P2', 1
  UNION ALL SELECT 'P3', 0
)

SELECT
  customerID
FROM
  Orders
INNER JOIN
  Products
    ON Orders.ProductID = Products.ProductID
GROUP BY
  customerID
HAVING
  COUNT(distinct Orders.ProductID) = (SELECT SUM(inclusive) FROM Products)
  AND MIN(Products.inclusive)      = 1

First, the join filters everything own to just Orders including ANY of P1, P2 or P3.

The GROUP BY makes these into groups of orders, one group for each customer.

The first HAVING clause looks at all the orders that are in that list. It counts how many different products there are in that sub-set. It checks how many inclusive products are in the Product list we're searching for. It stipulates that these two figures must be the same.
[In this example; they must have ordered exactly two different products.]

The second HAVING clause checks if any of those products has inclusive = 0.
[No product ordered by the customer may appear in the exclusion list.]


EDIT: This is an alternative, that some people seem to prefer, but I think that it is less performant (in cases where the Orders table is of any significant size).

SELECT
  customerID
FROM
  Orders
WHERE
  ProductID in ('P1', 'P2')
GROUP BY
  customerID
HAVING
  COUNT(distinct ProductID) = 2
  AND NOT EXISTS (SELECT *
                    FROM Orders AS lookup
                   WHERE CustomerID = Orders.CustomerID
                     AND ProductID IN ('P3')
                 )

Upvotes: 2

weenoid
weenoid

Reputation: 1186

DECLARE @Customer TABLE ( ID int, Name nvarchar(20) )
DECLARE @Order TABLE ( CustomerID int, Product nvarchar(2) )

INSERT INTO @Customer VALUES ( 1, 'Dave' )
INSERT INTO @Customer VALUES ( 2, 'Another Dave' )

INSERT INTO @Order VALUES ( 1, 'P1' )
INSERT INTO @Order VALUES ( 1, 'P2' )
INSERT INTO @Order VALUES ( 2, 'P1' )
INSERT INTO @Order VALUES ( 2, 'P2' )
INSERT INTO @Order VALUES ( 2, 'P3' )

SELECT          a.Name
FROM            @Customer a
INNER JOIN      @Order b ON ( b.CustomerID = a.ID AND b.Product = 'P1' )
INNER JOIN      @Order c ON ( c.CustomerID = a.ID AND c.Product = 'P2' )
LEFT OUTER JOIN @Order d ON ( d.CustomerID = a.ID AND d.Product = 'P3' )
WHERE           d.CustomerID IS NULL
GROUP BY        a.Name

Upvotes: 0

Diego
Diego

Reputation: 36156

assuming that Orders contains customerID and ProductId columns

select disrinct(customerID) from Orders

gives you all the customers that placed orders

select customerID, COUNT(distinct(productID))
from Orders
where productID in ('P1', 'P2') 
      and customerid not in (select customerID
from Orders
where productID in ('P3'))
group by customerID
having COUNT(distinct(productID))>1

gives you all the customers that placed orders with product P1 and P2 but not P3

Upvotes: 0

Related Questions