Pfthb

I'm new to SQL, and I'm asking for help in an apparently easy question, but it gets cumbersome in my mind.

I have the following table:

ID NAME CITY
---------------------
1 John new york
2 Sam new york
3 Tom boston
4 Bob boston
5 Jan chicago
6 Ted san francisco
7 Kat boston

I want a query that returns all the people who live in a city that another person registered in the database also lives in.

The answer, for the table I showed above, would be:

ID NAME CITY
---------------------
1 John new york
2 Sam new york
3 Tom boston
4 Bob boston
7 Kat boston

To avoid a correlated subquery which leads to a nested loop, you could perform a self join:

SELECT id, name, city
FROM persons
 JOIN (SELECT city
 FROM persons
 GROUP BY city HAVING count(*) > 1) AS cities
 USING (city);

This might be the most performant solution.

I would use EXISTS :

SELECT t.*
FROM table t
WHERE EXISTS (SELECT 1 FROM table t1 WHERE t1.city = t.city AND t1.name <> t.name);

This is really a two part question:

1. What cities have more than one user located in them?


2. What users live in that subset of cities?

Let's answer it in two parts. Let's also make the simplifying assumption (not stated in your question) that the Users table has only one entry per user per city.

To find cities with more than one user:

 SELECT City FROM Users GROUP BY City HAVING COUNT(*) > 1

Now, let's find all the users for those cities:

SELECT ID, User, City FROM Users 
 WHERE City IN (SELECT City FROM Users GROUP BY CITY HAVING COUNT(*) > 1)

This will give you the rows that have the same city more than 1 time:

SELECT persons.*
FROM persons
WHERE (SELECT COUNT(*) FROM persons AS p GROUP BY CITY HAVING p.CITY = persons.CITY) > 1

This is just a different flavor from the others that have posted.

 SELECT ID,
 name,
 city
 FROM (SELECT DISTINCT
 ID,
 name,
 city,
 COUNT(1) OVER (PARTITION BY city) AS cityCount
 FROM table) t
 WHERE cityCount > 1

This can be expressed many ways. Here is one possible way:

select * from persons p
where exists (
 select 1 from persons p2
 where p2.city = p.city and p2.name <> p.name
)