CODEWITHSUNDEEP
sqlpervasive
Josh Noe
Josh Noe

Reputation: 2796

SQL Select where two particular items not sold on same date to same customer

I'm trying to get all orders where item of type A is on the order, but item of type B is not also on the order. Unfortunately, the table structure has some deficiencies which make this a little more difficult than I thought it would be.

DB Issues:
-There is no "Orders" table, only a "Order_Lines" table. However, I can assume that any Order_Lines with the same "OrderDate" field and same customer (see below) are all part of the same order.

-A new "Customer" record is created for every transaction (yikes!), so the "CustID" field is useless. However, I can assume that any Customer who has the same "FirstName" and "LastName" is the same.

DB layout:

Order_Lines:
OrderLineID   CustID  ProductID      OrderDate
-----------   -----   -----------    ----------
10            5       50             2011-08-01
20            6       60             2011-08-01
30            7       50             2011-08-02
40            8       55             2011-08-03
50            9       70             2011-08-03

Customer:
CustID   FirstName   LastName
-----    ---------   ---------
5        Bill        Smith
6        Bill        Smith
7        Roger       Wilcock
8        Rudiger     Fensterbottom
9        Sam         Williams

ProductTypes:
ProductID   ProductType
---------   -----------
50          Kite
55          Kite
60          Yo-Yo
70          Yo-Yo

I'd like to get all orders where Kites were ordered, but Yo-Yos were not on the same order. My result set in this case would be:

ProductID   OrderDate   FirstName   LastName
---------   ---------   ---------   --------
50          2011-08-02  Roger       Wilcock
55          2011-08-03  Rudiger     Fensterbottom

Upvotes: 2

Views: 1029

Answers (2)

Tom H
Tom H

Reputation: 47392

SELECT
    OL1.*
FROM
    Order_Lines OL1
INNER JOIN Product_Types PT1 ON
    PT1.product_id = OL1.product_id AND
    PT1.product_type = 'Kite'
INNER JOIN Customers C1 ON
    C1.customer_id = OL1.customer_id
WHERE
    NOT EXISTS (
        SELECT *
        FROM Order_Lines OL2
        INNER JOIN Product_Types PT2 ON
            PT2.product_id = OL2.product_id
        INNER JOIN Customers C2 ON
            C2.customer_id = Order_Lines.customer_id AND
            C2.first_name = C1.first_name AND
            C2.last_name = C1.last_name
        WHERE
            OL2.order_date = OL1.order_date AND
            PT2.product_type = 'Yo-Yo')

Upvotes: 1

Brian Webster
Brian Webster

Reputation: 30865

Here's the basics. I'd go with Exists. My intention is to demonstrate a concept, not write the full code for you.

Select
 *
From
 OrderLines as OL
Where
 Exists (Select * from OrderLines as OL2 where OL2.ProductID = 50 and OL2.PatID = OL.PatID and OL2.OrderDate = OL.OrderDate) -- Has ProductID: 50
 and NOT Exists (Select * from OrderLines as OL3 where OL3.ProductID = 60 and OL3.PatID = OL.PatID and OL3.OrderDate = OL.OrderDate) -- Does not have ProductID: 60
  • Assumes SQL Server
  • You'll want to add joins over to the Customer Table on Name. I used PatID for simplicity sake.
  • You'll also want to add joins over to the product table on Prod Name if you don't know the ID's of Prod A and Prod B

Upvotes: 1

Related Questions