Hi there to all SQL gurus
So, here is the scenario. I have a #temp table in one of my SQL stored procedures which has only 2 columns, say Customer ID and Profile ID, and it has the below data
Customer ID Profile ID
100001 ABCD001
100001 ABCD002
100002 ABCD001
100002 ABCD002
100003 ABCD001
I need to write a query which selects only the Profile ID which is mapped to all the Customer IDs. In this case Customer ID 100001 and 100002 have both ABCD001 and ABCD002, but Customer ID 100003 has only Profile ID ABCD001, so, the output of the SQL should have only ABCD001. How do I do this without using a CURSOR? Thanks in advance
February 7, 2025 at 3:08 pm
SELECT [Customer ID]
FROM dbo.table_name
GROUP BY [Customer ID]
HAVING COUNT(DISTINCT [Profile ID]) = (SELECT COUNT(DISTINCT [Profile ID]) FROM dbo.table_name)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2025 at 6:54 pm
This gives me an output like the below
Customer ID
100001
100002
But I want to know which Profile ID is mapped to all the Customer IDs. Am expecting the output to be
Profile ID
ABCD001
February 7, 2025 at 7:02 pm
In addition, in the below case as well (where 100003 has the Profile ID ABCD0003), I still would like to see only ABCD001 (the profile that is common for all the Customer IDs) to be displayed in the output
Customer ID Profile ID
100001 ABCD001
100001 ABCD002
100002 ABCD001
100002 ABCD002
100003 ABCD001
100003 ABCD003
OOPS, SORRY, I did it back'ards.
SELECT [Profile ID]
FROM dbo.table_name
GROUP BY [Profile ID]
HAVING COUNT(DISTINCT [Customer ID]) =
(SELECT COUNT(DISTINCT [Customer ID]) FROM dbo.table_name)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2025 at 7:24 pm
Thanks a ton, it worked great ! I marked this as the answer, thanks again, you made my day !
I had a CURSOR to do this as a fallback in case this doesn't work, but the CURSOR method I used looks like stupid code and I have to create 2 temp tables just for that purpose. I hate CURSORS by the way 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply