Noize Noize - 1 month ago 7
MySQL Question

select only Duplicate value SQL

I have a table with quite a few columns, lets take this example

ID | Name | CarLicence | Job | Address | ... | ....
____________________________________________________
1 | Pete | 1234 |xxx | xxxxx | xxx | xxx
2 | John | 5555 |xxx | xxxxx | xxx | xxx
3 | Davi | 1234 |xxx | xxxxx | xxx | xxx
4 | Mira | 9999 |xxx | xxxxx | xxx | xxx
5 | Kira | 1234 |xxx | xxxxx | xxx | xxx


my goal is to display only the records with duplicated car Licence and their associated name, something like this would be desired

CarLicence | Name
__________________
1234 | Pete
1234 | Davi
1234 | Kira


so far i did

select CarLicence ,Name
From Table
group by CarLicence ,Name


but this will display duplicate and non duplicate, i want to display only the duplicated CarLicence.

Answer

Try something like this

SELECT *
FROM   Table t1
WHERE  EXISTS (SELECT 1
               FROM   Table t2
               WHERE  t1.CarLicence = t2.CarLicence
               HAVING Count(1) > 1) 

The sub-query will check the count of each CarLicence when it more than one the record will be returned