CODEWITHSUNDEEP
sql
Rizing
Rizing

Reputation: 21

Finding shared data in multiple columns

Here's my table "Earth"

http://imgur.com/EIlbKEN

I need to find all records / rows, in which people share

In this query, I can't use any specific country, state, or city names. Therefore is there a query I can use, that will still return for example two or more people who share the same country, state, and city?

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

What you are looking for is a self join. It would look something like this:

select t1.personid, t2.personid, t1.country, t1.state, t1.city
from t t1 join
     t t2
     on t1.country = t2.country and t1.state = t2.state and t1.city = t2.city and
        t1.personid < t2.personid;

Upvotes: 4

Related Questions