Another SQL Show all records in a Group By, with a HAVING

Question

We accidentally let in some duplicate clients

id	Lookup
1	abc
2	abc
3	def
4	def
5	ghi
6	ghi
7	j
8	k
9	l

I have a query that shows the duplicate Lookup values

SELECT Lookup, COUNT (Lookup)
  FROM Clients
  GROUP BY Lookup
  HAVING COUNT(*) > 1

That produces

Lookup	(No name)
abc	2
def	2
ghi	2

I'd like to see the id numbers next to those.

id	Lookup
1	abc
2	abc
3	def
4	def
5	ghi
6	ghi

I found MySQL - Group and total, but return all rows in each group which looks very close to what I'm doing. But:

  SELECT a.id, a.Lookup
  FROM Clients as a
  JOIN (SELECT id, Lookup
  FROM Clients
  GROUP BY Lookup, id
  HAVING COUNT(*) > 1) as b
  ON a.id = b.id

Produces an empty result set. (I tried with and without the Lookup in "JOIN (SELECT id, Lookup").

That looks to me like it should work. What am I overlooking?

Answer 1

You must join it on the Lookup instead of id

SELECT c.*
FROM Clients c
JOIN (
    SELECT Lookup, COUNT (Lookup)
    FROM Clients
    GROUP BY Lookup
    HAVING COUNT(*) > 1
) cnt ON cnt.Lookup = c.Lookup