h69adWN7

1SELECT using GROUP BY and HAVING not returning records
2If EXISTS( SELECT name FROM sysobjects WHERE name = 'USER_DUPLICATES' AND type = 'U' ) 
3    DROP TABLE USER_DUPLICATES
4GO
5
6CREATE TABLE USER_DUPLICATES
7(
8    FIRSTNAME VARCHAR(MAX),
9    LASTNAME VARCHAR(MAX),
10    PHONE VARCHAR(MAX),
11    EMAIL VARCHAR(MAX),
12    TITLE VARCHAR(MAX),
13    LMU VARCHAR(MAX)
14)
15GO
16
17INSERT INTO USER_DUPLICATES
18(
19    FIRSTNAME,
20    LASTNAME,
21    PHONE,
22    EMAIL,
23    TITLE,
24    LMU
25)
26SELECT
27    FIRSTNAME,
28    LASTNAME,
29    PHONE,
30    EMAIL,
31    TITLE,
32    LMU
33FROM TM_USER
34GROUP BY
35    FIRSTNAME,
36    LASTNAME,
37    PHONE,
38    EMAIL,
39    TITLE,
40    LMU
41HAVING COUNT(LASTNAME) > 1
42	
43HAVING COUNT(LASTNAME) > 1
44	
45HAVING COUNT(LASTNAME) > 0
46	
47SELECT
48    a.FIRSTNAME,
49    a.LASTNAME,
50    a.PHONE,
51    a.EMAIL,
52    a.TITLE,
53    a.LMU
54FROM TM_USER a
55INNER JOIN
56(
57    SELECT LASTNAME
58    FROM TM_USER
59    GROUP BY LASTNAME
60    HAVING COUNT(1) > 1
61) b ON a.LASTNAME = b.LASTNAME
62	
63Select LAST_NAME from TM_USER  GROUP BY LAST_NAME HAVING COUNT(LAST_NAME) > 1
64	
65SELECT LASTNAME FROM TM_USER GROUP BY LASTNAME HAVING COUNT(LASTNAME) > 1