SELECT wont work using IN()

  • This code works perfectly,

    SELECT distinct Customer_Number, SUM(Price) As Total

    FROM Customer_Offer x1

    WHERE

    EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number

    AND x2.Product_Offer = 'prod1')

    AND NOT EXISTS

    (SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND

    x3.Product_Offer IN ( 'prod2','prod3', 'prod4') )

    GROUP BY Customer_Number

    ORDER BY Customer_Number;

    however I would like to find customers who have bought prod1 and prod2 and not bought prod3 and prod4, I tried using this code

    SELECT distinct Customer_Number, SUM(price) As Total

    FROM Customer_Offer x1

    WHERE

    EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number

    AND x2.product_offer IN ('prod1', prod2)

    AND NOT EXISTS

    (SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND

    x3.product_offer IN ( 'prod3','prod4') )

    GROUP BY Customer_number

    ORDER BY Customer_Number;

    but get a syntax error! is anyone able to point me in the right direction for what I am trying to achieve?

    I have also tried using an additional EXISTS like this SELECT distinct Customer_Number, SUM(Price) AS Customer_Value

    FROM Customer_Offer x1

    WHERE

    EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number

    AND x2.Product_Offer = 'prod1')

    AND EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number

    AND x2.Product_Offer = 'prod2')

    AND NOT EXISTS

    (SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND

    x3.Product_Offer IN ( 'prod3', 'prod4') )

    GROUP BY Customer_Number

    ORDER BY Customer_Number;

    This also doesnt work, the syntax seems correct as it returns no columns so leaving it to be a problem with my logic!

  • soulchyld21 (2/16/2012)


    however I would like to find customers who have bought prod1 and prod2 and not bought prod3 and prod4, I tried using this code

    SELECT distinct Customer_Number, SUM(price) As Total

    FROM Customer_Offer x1

    WHERE

    EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number

    AND x2.product_offer IN ('prod1', prod2)

    AND NOT EXISTS

    (SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND

    x3.product_offer IN ( 'prod3','prod4') )

    GROUP BY Customer_number

    ORDER BY Customer_Number;

    but get a syntax error! is anyone able to point me in the right direction for what I am trying to achieve?

    Your syntax error is "prod2". Try

    SELECT distinct Customer_Number, SUM(price) As Total

    FROM Customer_Offer x1

    WHERE

    EXISTS

    (SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number

    AND x2.product_offer IN ('prod1', 'prod2'))

    AND NOT EXISTS

    (SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND

    x3.product_offer IN ( 'prod3','prod4') )

    GROUP BY Customer_number

    ORDER BY Customer_Number;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Something like this should also work:

    ; With CustomerSelection AS

    (

    SELECT

    Customer_Number,

    SUM (CASE WHEN Product_Offer = 'prod1' THEN 1 ELSE 0 END) AS Prod1

    SUM (CASE WHEN Product_Offer = 'prod2' THEN 1 ELSE 0 END) AS Prod2

    SUM (CASE WHEN Product_Offer = 'prod3' THEN 1 ELSE 0 END) AS Prod3

    SUM (CASE WHEN Product_Offer = 'prod4' THEN 1 ELSE 0 END) AS Prod4

    FROM

    Customer_Offer

    GROUP BY Customer_Number

    )

    SELECT

    Customer_Number

    SUM(Price) AS Customer_Value

    FROM

    Customer_Offer

    LEFT JOIN CustomerSelection on Customer_Offer.Customer_Number=CustomerSelection.Customer_Number

    WHERE

    CustomerSelection.Prod1 > 0

    AND CustomerSelection.Prod2 > 0

    AND CustomerSelection.Prod3 = 0

    AND CustomerSelection.Prod4 = 0

  • I seem to have overcome my initial issue but come up to another dead end (so to say) and cant make out what your query is doing exactly, I am trying to write a query that will give me customers who have bought product1 and product2 but not bought product3 and product4, in my table, Customer_offer I have records of which customer has bought a certain product obviously some customers have bought more than one product, I seem to be having trouble with the fact that Im getting customers who have bought one product of the two ('BACNZCT1','BAC1PTS') and not both using this query, could anyone help me to understand where Im getting it wrong and how I can fix it! Customers who have bought BACNZCT1 have to have bought BAC1PTS (Because BACNZCT1 is an add on) so how can i structure my query to not give me those who have bought BAC1PTS ONLY but both!

    SELECT DISTINCT Customer_Number, SUM(Price) As Total_Value FROM Customer_Offer x1

    WHERE

    EXISTS (SELECT * FROM Customer_Offer x2

    WHERE

    EXISTS(SELECT * FROM Customer_Offer WHERE x2.Customer_Number = x1.Customer_Number AND x2.Product_Offer IN('BACNZCT1','BAC1PTS')))

    AND NOT EXISTS (SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND x3.Product_Offer IN ( 'BACNLCT1', 'BACGSCT1'))

    GROUP BY Customer_Number

    ORDER BY Customer_Number

  • This was removed by the editor as SPAM

  • The first section (the CTE) is to determine if a customer has ever bought a certain item:

    SELECT

    Customer_Number,

    SUM (CASE WHEN Product_Offer = 'prod1' THEN 1 ELSE 0 END) AS Prod1

    SUM (CASE WHEN Product_Offer = 'prod2' THEN 1 ELSE 0 END) AS Prod2

    SUM (CASE WHEN Product_Offer = 'prod3' THEN 1 ELSE 0 END) AS Prod3

    SUM (CASE WHEN Product_Offer = 'prod4' THEN 1 ELSE 0 END) AS Prod4

    FROM

    Customer_Offer

    GROUP BY Customer_Number

    You can use the value of the columns: Prod1,Prod2,Prod3,Prod4 to determine if the customer has bought the item.

    If the customer has purchased prod1 10 times (10 lines in Customer_Offer) the value of column PROD1= 10,

    If the customer has purchased prod2 5 times (5 lines in Customer_Offer) the value of column PROD2= 5,

    If the customer has purchased prod3 0 times (0 lines in Customer_Offer) the value of column PROD2= 0,

    So if the value of a certain column =0 then the combination of the product/customer does not exist. With a where clause you can then filter the customers based on that value: if you want to get only the customers who have bought Prod4 and nothing else:

    Where PROD1=0 AND PROD2=0 AND PROD3=0 AND PROD4>0

    if you want to get only the customers who have bought Prod2 and prod3 and not prod1:

    Where PROD2>0 AND PROD3>0 AND PROD1=0

    if you want to get the customers who have bought at least Prod2

    Where prod2>0

  • soulchyld21 (2/16/2012)


    I seem to be having trouble with the fact that Im getting customers who have bought one product of the two ('BACNZCT1','BAC1PTS') and not both using this query, could anyone help me to understand where Im getting it wrong...

    Absolutely. Read the article at the first link in my signature line below and post some readily consumable test data using the methods from that article. That'll give everyone some common ground with you to compare notes with and easily discuss possible problems like your having.

    Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply