Sql query for extracting info from one table based on other

Question

I have two tables

Country with countryid and countryname
City with cityid and cityname

I want to extract city names based on the countryid I select.

I'm very new to sql database and please help me with direct query if you can.

Thank you very much.

You need a 'countryid' field in your City table in order to do the join. — MillaresRoo, Dec 15 '13 at 11:25

score 3 · Accepted Answer · answered Dec 15 '13 at 11:28

3

According to the table structure which you showed, this query is not possible. You need to add a 'country_id' to the cities table so that you know in which country is each city. Once this has been done, your query would be

select cities.cityname, countries.countryname
from cities inner join countries on countries.country_id = cities.country_id
order by countries.countryname, cities.cityname

answered Dec 15 '13 at 11:28

No'am Newman

6,395
5
38
50

thanks a lot for your help.... but how would the countryid in the city table fill up ??! am i suppose to put that myself ??! – Varun Barve Dec 15 '13 at 11:40
@user154537: The country should be added every time that you add a city. – No'am Newman Dec 15 '13 at 17:24
ok... thanks a lot for your help... it worked... can i put a where clause in the above query ?? if yes, where – Varun Barve Dec 15 '13 at 18:42
@user154537: a 'where' clause would come after the 'from' clause and before the 'order' clause – No'am Newman Dec 16 '13 at 04:16
@user154537: If my (or any other) answer helped you, then mark it as *the* answer. Read the FAQ if you need help doing this. – No'am Newman Dec 18 '13 at 04:11

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

First you should have countryid in city table as a country can have many cities. Then you can write.

select A.cityname from city A
where
    A.countryid in (select B.countryid from country B)

Please check this link if you want to find out more.

SQL: Select from one table matching criteria in another?

edited May 23 '17 at 12:19

Community

1
1

answered Dec 15 '13 at 11:28

Bibek Maharjan

549
2
5
15

1

Why use a subquery instead of a simple inner join? – No'am Newman Dec 15 '13 at 11:40
@No'am Newman yes, it can be solved by using inner join also. That's why I have provided link which has both the solutions. Thanx for comment. – Bibek Maharjan Dec 15 '13 at 11:43

score 0 · Answer 3 · answered Dec 15 '13 at 11:29

0

You have to add a countryid in City table and write the following query

select * from city c left outer join country co on c.countryid=co.countryid;

answered Dec 15 '13 at 11:29

Balaji Perumal

830
1
6
20

score 0 · Answer 4 · answered Dec 15 '13 at 11:35

0

You need to have the countryid in the city table.

    SELECT cityid, cityname FROM city WHERE countryid = $CounrtyID

answered Dec 15 '13 at 11:35

Cyril Joudieh

132
2
15

Sql query for extracting info from one table based on other

4 Answers4