Forum Discussion

KrishnaGceb's avatar
KrishnaGceb
Copper Contributor
Feb 07, 2025

Need a solution for a problem without using CURSOR

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? Would a CTE help? I am not very familiar with CTE, so if the solution is using a CTE, please give your suggestions in more detail. Thanks in advance

1 Reply

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Feb 09, 2025

    CTE is not required, and joins and subqueries have the same effect. To get result, you can think as followed steps(Assume there are no dupilcate mappings):

    1. Get a unique [Profile ID] list. (By DISTINCT)
    2. Count the number of rows for each [Profile ID] mapping to [Customer ID]. (Using CROSS APPLY)
    3. Get the total number of [Customer ID]. (Using COUNT(DISTINCT fieldname))
    4. Filter for [Profile ID] where the number of mapped rows is equal to the total number of [Customer ID].(INNER JOIN)

    Code

    CREATE TABLE #temp
(
	[Customer ID] varchar(10),
	[Profile ID] varchar(10)
)

INSERT INTO #temp
([Customer ID], [Profile ID])
VALUES
 ('100001', 'ABCD001')
,('100001', 'ABCD002')
,('100002', 'ABCD001')
,('100002', 'ABCD002')
,('100003', 'ABCD001')

SELECT DISTINCT [Profile ID]  --Get unique [profile id] list
FROM #temp t
CROSS APPLY
	(SELECT COUNT(*) AS pcc FROM #temp WHERE [Profile ID]=t.[Profile ID]) a --Count the number of rows for each [Profile ID] mapping to [Customer ID]
INNER JOIN 
	(SELECT COUNT(DISTINCT [Customer ID]) AS [count of customer] FROM #temp) c --Get the total number of [Customer ID]
ON a.pcc=c.[count of customer] --Get result

DROP TABLE #temp

     

Resources