mysql get duplicates of one value

Is there a way开发者_StackOverflow in mysql to check one specific column and see if the value occurs more then one time, and if so get the row.

lets say that my table looks like this:

id | first_name | last_name | age
1     john           doe       20
..    .......       .....     ...
..    .......       .....     ...

So I want the mysql to go and bring back a list where the age is 18 AND where the first_name occurs more then once.

SELECT y.id, y.first_name, y.last_name, y.age
    FROM (SELECT first_name
              FROM YourTable
              WHERE age = 18
              GROUP BY first_name
              HAVING COUNT(*) > 1) t
        INNER JOIN YourTable y
            ON t.first_name = y.first_name
    WHERE y.age = 18;

You can use a JOIN:

SELECT DISTINCT T1.first_name
FROM yourtable T1
JOIN yourtable T2
ON T1.id < T2.id
AND T1.first_name = T2.first_name
AND T2.age = 18
WHERE T1.age = 18

select id, first_name, last_name, age
from YourTable where first_name in
(
    select first_name
    from YourTable 
    where age = 18
    group by first_name
    having count(first_name) > 1
)