'mysql query user stay in same city and same country

I have a table users thas has the following fields

id, name, city id, country id

How can I select users who are in the same city and same country?

Example

id   name cityid countryid
1    ram   1       2
2    sam   5       2
3    bil   1       2
4    roy   4       6
5    bin   5       2
7    abu   7       5

What is the MySQL query so I can fetch only user id 1 and 3 having same city id 1 and country id 2 and user id 2 and 5 for same city if 5 and country id 2.

mysqlsql


Solution 1:[1]

SELECT cityid, countryid, GROUP_CONCAT(name) names
FROM users
GROUP BY cityid, countryid
HAVING COUNT(*) > 1

Solution 2:[2]

You can join the table onto itself, and ask which city & country is the same. Be sure to specify that the userid's must be different in your where clause, because every user lives in the same city & country as itself.

SELECT u1.id, u2.id, u1.name, u2.name, u1.cityid, u2.cityid /*or whatever fields you want*/
FROM users u1
JOIN users u2
WHERE u1.id < u2.id
and u1.cityid = u2.cityid
and u1.countryid= u2.countryid

Solution 3:[3]

The same country would be like this:

INNER JOIN (SELECT countryid FROM users GROUP BY countryid HAVING count(*) > 1) AS countries USING (countryid)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Barmar
Solution 2 Konerak
Solution 3 Matboyi

Related Questions