CODEWITHSUNDEEP
mysqlsql
user2014429
user2014429

Reputation: 2577

selecting region names based on country and region codes

I have a table called profile which has a column region_code and country_code and another table called regions which has columns country_code, region_name and region_code. I want to select the region_name from table regions based on the region_code and country_code from table profiles. I thought the code below wouldwork but it always returns an empty set and I can't figure out what I'm doing wrong. Any help much appreciated.Thanks.

SELECT region_name 
FROM regions 
WHERE country_code IN(
                       SELECT country_code 
                       FROM profiles 
                       WHERE account_name = 'me'
                      )
AND region_code IN(
                       SELECT region_code
                       FROM profiles 
                       WHERE account_name = 'me'
                      )

Upvotes: 0

Views: 3567

Answers (3)

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

Use JOIN queries. Makes your query sweet and simple.

SELECT p.region_name
FROM region r
LEFT JOIN profile p
ON r.region_code = p.region_code
AND r.country_code = p.country_code
WHERE p.account_name = "me"

The query you posted is alright. The reason you're not getting any data is maybe because you don't actually have any data for account_name = "me" in profile table. Or maybe your database name has changed. Whatever, your query's ok, but JOIN is simpler.

Upvotes: 0

fthiella
fthiella

Reputation: 49069

I would simplify your query, and use something like this one:

SELECT region_name
FROM
  regions
WHERE
  (country_code, region_code) IN (SELECT country_code, region_code
                                  FROM   profiles
                                  WHERE  account_name = 'me')

but if you still get empty result, I would execute just the subquery and see what rows it returns:

SELECT country_code, region_code
FROM   profiles
WHERE  account_name = 'me'

i suspect that it doesn't return any value, or that the values returned are not present in the regions table.

Upvotes: 1

jpw
jpw

Reputation: 44891

It sounds like you want a join?

SELECT r.region_name 
FROM regions r
JOIN profiles p ON r.country_code = p.country_code 
               AND r.region_code  = p.region_code
WHERE p.account_name = 'me'

This would list the region name for the region that maps to the specific users region code and country code.

Upvotes: 2

Related Questions