1

cities

id name county_id
1 City1 1
2 City2 1
3 City3 2
4 City4 2

counties

id name
1 County1
2 County2

Hello,
I would like to select all city names from related county by enetring a city name.
I can do it via subquery, but is there any other way to achieve this?

SELECT name
FROM cities
WHERE cities.county_id =
      (SELECT county_id
       FROM cities
       WHERE name = 'City1'
       LIMIT 1);

result => "City1, City2"

Thank you.

1
  • 2
    You could use a join - but what's wrong with the subquery? Sep 9 '21 at 12:47
0

You can use join:

select name from cities c
inner join 
( SELECT county_id 
  FROM cities 
  WHERE name = 'City1' 
  LIMIT 1) as d 
on c.county_id=d.county_id ;

Demo: http://sqlfiddle.com/#!15/905fc/1

Not the answer you're looking for? Browse other questions tagged or ask your own question.