CODEWITHSUNDEEP
mysqlsql
Frank Nwoko
Frank Nwoko

Reputation: 3934

MYSQL Statement

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?

Upvotes: 3

Views: 82

Answers (3)

magma
magma

Reputation: 8520

Frank,

.. WHERE country_id = (SELECT id,country_id FROM users ..

you are comparing a single value (country_id) to a two-column result set (id, country_id). This is wrong.

Your query, rewritten, would be:

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

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882078

For a start, you shouldn't be selecting the id fields within the subqueries.

Instead, look into using something like:

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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'

Upvotes: 4

Related Questions