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] : /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