mysql query to find records with same multiple fields

Question

if I have a mysql table person and I want to find the person.id's of the people that have same name and from the same country, how would I do that?

something like:

select * 
from person p 
join person p2 
where p.country = p2.country and p.name = p2.name

but the example above if obviously not the way to go :\

Answer 1

Your JOIN is incorrect, try

SELECT p.id as first, p2.id as duplicate 
FROM person p 
JOIN person p2 ON p.country = p2.country AND p.name = p2.name

Answer 2

You are using Where keyword however in sql we always use ON with joins so

replace

where  p.country = p2.country and p.name = p2.name

to

ON  p.country = p2.country and p.name = p2.name