MySQL subquery with IN clause

Question

This query works fine:

SELECT country_name FROM countries WHERE country_id IN (23,86,79)

so I can get the list of countries' name based on those country_id. But what I want is to get them with a subquery like:

SELECT country_name  FROM countries WHERE country_id IN (SELECT office_id FROM countries WHERE country_code='FRA')

I can get the first name of the office Id and not all (in this case should be 4). The office_id and country_id are Int. Any helps?

Why you dont use this ?? - SELECT country_name FROM countries WHERE country_code='FRA' — Bernd Buffen
– Bernd Buffen, Commented Jan 23, 2016 at 20:17
the query looks fine. show us some data and expected results so we can see what goes wrong — Nir Levy
– Nir Levy, Commented Jan 23, 2016 at 20:26

Rahul · Accepted Answer · 2016-01-23 20:37:58Z

2

why do you need such wrong/long turn query. your posted query (below) with subquery

SELECT country_name 
FROM countries 
WHERE country_id IN ( SELECT office_id 
                      FROM countries 
                      WHERE country_code='FRA' )

Can be simpliy

SELECT country_name 
FROM countries 
WHERE country_code='FRA';

answered Jan 23, 2016 at 20:37

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ras Over a year ago

The subquery returns several id (es: 12,22,43,9). With these id I need to get the corresponding country name, The problem if that if I wrote something like "...IN (12,22,43,9)" the whole query works fine and I get four rows but with the Select as subquery I get only one value

Rahul Over a year ago

You don't need the subquery in first place. just use the query in answer with the required where condition ... should work fine. if not then do select count(*) from countries where country_code='fra' and see what's the count ... that will tell you what's wrong.

Ras Over a year ago

Sorry guys, I've been not so clear. the inner query, "SELECT office_id FROM countries WHERE country_code='FRA'", returns a string with comma separated values (es: "12,22,223") and I would use this with IN clause

Rahul Over a year ago

Ohh!! man ... no that's a comma separated list and you can't use it in IN caluse since it's not properly quoted. You should consider normalizing your table structure rather.

Collectives™ on Stack Overflow

MySQL subquery with IN clause

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related