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?
>Solution :
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