February 8, 2012 at 8:48 am
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = 'Offer2'
AND Product_Offer = 'Offer1'
Hi, I have come across an unusual error, The above code does not return any values, but if I select top 1000 rows from the Customer_Officer table I can see that both offers do exist and are indeed in the table, however they are in different rows! Could it be a design flaw? I need a query that will return the customer number for a customer that has bought two different offers! Running the above query with only one condition, (Where) returns the correct results under either condition but not under both!
February 8, 2012 at 8:53 am
Change the AND to an OR and give that a try.
February 8, 2012 at 8:53 am
You cannot use AND clause for what you need here. Try this
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer IN ( 'Offer2','Offer1')
February 8, 2012 at 8:55 am
Thank you!!:-D
February 8, 2012 at 8:55 am
After a reread, you are looking for a query that will return the customers that have purchased both "offer1' and 'offer2' and each of these are represented as individual rows in the table, correct?
February 8, 2012 at 10:43 am
Something like this?
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = 'Offer2'
and Custom_Number in
(
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = 'Offer1'
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 3:39 pm
Yes that is correct, It seems that using IN() will do the trick!
To find a customer that has purchased one product and not another can I use somthing like this?
SELECT Customer_Number FROM Table
WHERE Product_Number = '1' and Product_Number NOT IN ('2', '3')🙂
February 8, 2012 at 3:50 pm
Yes. You can do that and if Product_Number column is int/bigint you dont need to put it in quotes
SELECT Customer_Number FROM Table
WHERE Product_Number = 1
AND Product_Number NOT IN (2,3)
February 8, 2012 at 4:16 pm
SQLback (2/8/2012)
Yes. You can do that and if Product_Number column is int/bigint you dont need to put it in quotes
SELECT Customer_Number FROM Table
WHERE Product_Number = 1
AND Product_Number NOT IN (2,3)
That doesn't actually do what you want. Anytime Product_Number = 1 it already is not in 2 or 3. You would have to a subquery like the one I posted above.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 5:39 pm
Thanks for correcting me Sean. I am glad you caught it.
February 8, 2012 at 6:01 pm
Something like:
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = '1'
and Customer_Number NOT IN
(
SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = '2' OR Product_Offer = '3'
)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply