t777 t777 - 1 month ago 5
MySQL Question

How to SQL select duplicates by one field and differs by another

I have the table

person_log
with the following fields:


  • id

  • timestamp

  • first_name

  • last_name

  • action



with some example data

1;012545878;homer;simpson;eating
2;812345222;homer;simpson;watching tv
3;312322578;marge;simpson;cooking
4;114568568;bart;simpson;skating
5;015345345;bart;simpson;skating


Now I need to select all recordsets where
first_name
and
last_name
is the same and occurs at least two times, but
action
differs.

Resulting in selecting
id
s 1 and 2.

How can I do that? TIA?

vkp vkp
Answer

Use a derived table to get the persons having atleast 2 distinct actions and join it to the original table to get the other columns in the result.

select p.*
from person_log p 
join (select first_name,last_name
      from person_log
      group by first_name,last_name
      having count(*) >=2 and count(distinct action) >= 2) p1
on p1.first_name=p.first_name and p1.last_name=p.last_name
Comments