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 Custo...
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