Select all the clients whose name is repeated

Question

Select all the clients whose name is repeated

Navigation

#1 by (1 votes)

2

To get all the clients out without repeating the names is very easy.

SELECT distinct firstname FROM client

And to get all the clients whose name is repeated more than once is not very complicated either.

SELECT count(*), firstname FROM client
GROUP BY firstname
HAVING COUNT(*) > 1;

What I can not get out is a list of all the clients whose name is repeated, so that if there are two "Juan" show me the record of each one.

mysql sql

asked by Takyo 02.10.2017 в 17:15

source

1 answer

In an sql query, is there an order to declare the join? Store files from a directory in array

score 1 · Accepted Answer

I can think of two ways

SELECT c.*
       FROM client c
       INNER JOIN (SELECT firstname FROM client GROUP BY firstname
                      HAVING COUNT(*) > 1
                  ) m
       ON m.firstname = c.firstname;

Or else:

SELECT c.*
       FROM client c
       WHERE firstname IN (SELECT firstname FROM client GROUP BY firstname
                      HAVING COUNT(*) > 1);