asked
How to find customer having only one List Assigned to them
Morning Everyone! I'm having a difficult time trying to write a query that will return a list of customerIDs having only one list assigned to them where the ListID is 114. ![alt text][1] [1]: /storage/temp/980-results.jpg There are 46041 rows in this table and in the above image I would like the results to show CusNid 6 since this has 114 ONLY as their assigned List. I need to get a list of CusNids that may have ONLY 114 Listid assigned to them. Can someone please help me out? This is probably simple to do but I'm having brain freeze on how to get this result set. Any assistance is greatly appreciated.
Thank you! That helped me get where I need to be. I added a JOIN to do a select on the query you had given to look for only the ListID = 114 and with your query and the added JOIN I was able to get the customers that ONLY have 114 list on their account. No this was not a homework assignment just a major brain freeze on my end. SELECT a.cusnid, b.ListNid FROM ( SELECT CusNid FROM dbo.CustomerLists GROUP BY CusNid HAVING COUNT(*) = 1 ) a JOIN ( SELECT * FROM dbo.CustomerLists WHERE ListNid = 114 )b ON b.cusnid = a.cusnid
That is no problem at all on my end. You gave me the largest missing piece I was having an issue with. :) I was doing something similar you your count but I was originally looking at the ListID and not the count based on how many times the CUSTOMER appears. Thank you so much! Have a great afternoon!
Good afternoon. SELECT CusNid FROM CustomerLists GROUP BY CusNid HAVING COUNT(*) = 1 Unless, of course, this is a homework question, in which case the answer is "so you want to SELECT the customer IDs which [HAVE][1] a [COUNT][2] of 1 List... [1]:
http://msdn.microsoft.com/en-us/library/ms180199.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms175997.aspx
14 People are following this question.
