I have an Empl table in which there is a LastName field. How can I display entries for which this field is not unique (that is, all namesakes)?

    3 answers 3

    select * from Empl where LastName in( select lastname from empl group by lastname having count(*) > 1)
    • But I even like this one more. Not sure if it is faster, but definitely more readable - knes
     select `e1`.* from `Empl` as `e1`, (select `LastName` from `Empl` group by `LastName` having count(*)>1) as `e2` where `e1`.`LastName`=`e2`.`LastName`

    At the interviews like to give such.
    upd: sorry, comma forgot

      Stupid in the forehead

       select distinct * from empl where lastname in( select t1.lastname from empl t1, empl t2 where t1.lastname=t2.lastname and not t1.id=t2.id ) t;