Help with select query please
-
Hi,
Please could someone help me with this query? I don't have any test data at present.
Basically there will be a table as such:
CustomerNumber ProductID
----------------- -----------
00000000000001 AAA123
00000000000002 AAA121
00000000000003 AAA111
00000000000001 AAA121
00000000000003 AAA123
00000000000004 AAA121
There will be other columns including an Order ID (which will be the primary key).
What I need is a query which will return three lists:
1. All customers that have purchased AAA123 AND NOT AAA121
2. All customers who have purchased AAA121 AND NOT AAA123
3. All customers who have purchased BOTH AAA121 AND AAA123
Thank you in advance for your help.
Regards,
Sunil
-
Have a look at exists and not exists in BOL, that should do for you!
---------------------------------------------------------------------------------
-
You can do the first with something like this. The others should be similar.
SELECT Customer
FROM mytable
GROUP BY Customer
HAVING SUM(CASE WHEN ProductID='AAA123' THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN ProductID='AAA121' THEN 1 ELSE 0 END)=0
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Many thanks for both your replies.
I've made a quick test table and tried it with Mark's method, it worked a treat.
It was definitely useful reading up on EXISTS/NOT EXISTS on BOL too.
Regards,
Sunil
-
Mark's approach is good!
I guess this should also do.
CREATE TABLE #SORDER (CID int, PID varchar(20))
INSERT INTO #SORDER VALUES (0000001 ,'AAA123')
INSERT INTO #SORDER VALUES (0000002 ,'AAA121')
INSERT INTO #SORDER VALUES (0000003 ,'AAA111')
INSERT INTO #SORDER VALUES (0000001 ,'AAA121')
INSERT INTO #SORDER VALUES (0000003 ,'AAA123')
INSERT INTO #SORDER VALUES (0000004 ,'AAA121')
Select * from #SORDER T1
WHERE T1.PID = 'AAA121'
AND NOT EXISTS (SELECT 1
FROM #SORDER T2
Where T1.CID = T2.CID
AND T2.PID = 'AAA123')
Select * from #SORDER T1
WHERE T1.PID = 'AAA123'
AND NOT EXISTS (SELECT 1
FROM #SORDER T2
Where T1.CID = T2.CID
AND T2.PID = 'AAA121')
Select * from #SORDER T1
WHERE T1.PID = 'AAA123'
AND EXISTS (SELECT 1
FROM #SORDER T2
Where T1.CID = T2.CID
AND T2.PID = 'AAA121')
---------------------------------------------------------------------------------
-
That's fab Nabha, very useful indeed.
Many thanks.
Sunil
-
With regards to not having any test data, you can download this software for free for 14 days.... I used it recently and it works a treat...
http://www.red-gate.com/products/SQL_Data_Generator/index.htm
-
Brilliant, thanks Lewis.
Sunil
-
NP,
If you find that you need to use it again once the 14 day trial expires then you can type 'i need more time' in the text box which asks for your serial / activation code. I can not remember if you need to type it as one word or not by try both, i.e. ineedmoretime.
If this doesn't work then you can contact redgate and tell them you are still testing the software on your system and they will tell you how to reactivate the trial period.
You can do this with any of their products. 😀
-
Excellent tip, thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply