MYSQL Statement

Question

I want to select users whom have the same city_id and country_id with the current user.

I intentionally allowed country and city to be independent because some flexibility reasons.

does is it look like something like this?

SELECT id, country_id, city_id, surname FROM users WHERE 
   country_id = (SELECT id,country_id FROM users WHERE id = 'current_user') AND 
   city_id = (SELECT id, city_id FROM users WHERE id = 'current_id')

any ideas pls?

Answer 1

SELECT u2.id, u2.country_id, u2.city_id, u2.surname 
    FROM users u1
        INNER JOIN users u2
            ON u1.country_id = u2.country_id
                AND u1.city_id = u2.city_id
                AND u1.id <> u2.id
    WHERE u1.id = 'current_id'