CODEWITHSUNDEEP
phpmysqlsql
halliewuud
halliewuud

Reputation: 2785

mysql get duplicates of one value

Is there a way 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.

Upvotes: 3

Views: 586

Answers (3)

Mark Byers
Mark Byers

Reputation: 837966

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

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

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
)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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;

Upvotes: 5

Related Questions