Hi, perhaps I did not explain myself clearly, so I am trying to fix that.

I need to search a table with user records, find only those users that occur more than once.

I could just use the username (Student) to find the duplicates, but unfortunately half of their IDNo's are NULL, so I it's you cannot say they are duplicates. The only other way to check if they are duplicates is to check Tel3 and Tel1 (to try and check if they are duplicates).

Since are few forum users are asking for ddl, below I have provided it. It works to a certain extent.

And NO, this is not for an exam, interview questionnaire, study topic, etc.

CREATE TABLE #Student (

[Student] [varchar](30) NULL,

[IDNo] [varchar](13) NULL,

[Tel3] [varchar](12) NULL,

[Tel1] [varchar](12) NULL,

[Sort] [varchar](12) NULL

)

INSERT INTO #Student

SELECT 'Alison Stew' as Student, '405088' as IDNo, '+77721377200' as Tel3, '+77721377200' as Tel1, NULL

INSERT INTO #Student

SELECT 'Alison Stew' as Student, NULL as IDNo, '+77721377200' as Tel3, '+77721377200' as Tel1, NULL

INSERT INTO #Student

SELECT 'Amber Egno' as Student, NULL as IDNo, '+7677170700' as Tel3, '+76772476573' as Tel1, NULL

INSERT INTO #Student

SELECT 'Amber Egno' as Student, NULL as IDNo, '+76772476573' as Tel3, '+76772476573' as Tel1, NULL

INSERT INTO #Student

SELECT 'Carmen Schw' as Student, '083085' as IDNo, '+77711498898' as Tel3, '+77117642110' as Tel1, NULL

INSERT INTO #Student

SELECT 'Carmen Schw' as Student, NULL as IDNo, '+77117642110' as Tel3, '+77117642110' as Tel1, NULL

-------------------------------------------------------------------------------------------------

SELECT

[Student]

,IDNo

,Tel1

,Tel3

INTO #FilterDupes

FROM #Student

-------------------------------------------------------------------------------------------------

SELECT

[Student]

--,IDNo

--,Tel1

--,Tel3

,count([Student]) as Dupes

FROM #FilterDupes

GROUP BY

[Student]

--,IDNo

--,Tel1

--,Tel3

HAVING COUNT([Student]) > 1

DROP TABLE #Student

DROP TABLE #FilterDupes