SQL query in two tables
I have two tables:
Table: cities
city_id, lat, lon, name, country_id
Table: Countries
country_id, name
I want the SQL query to return:
cities.lat, cities.lon, cities.name, countries.name(for the corresponding cities.country_id)
I swear it should be easy. But I cannot get it to work.
+3
Sazal
source
to share
5 answers
You can use a connection for this. Also, always use the table name alias to understand a simple and standard rule.
select
ci.lat, ci.lon, ci.name, cn.name
from
city ci
inner join
countries cn on ci.country_id = cn.country_id
+1
Ajay2707
source
to share
If I got your point, this is a request.
SELECT cities.lat, cities.lon, cities.name, countries.name
FROM cities INNER JOIN countries
ON cities.country_id = countries.country_id
By the way, thought you can see what is cities.country_id
referring to countries.country_id
, you should have stated it explicitly.
0
taufique
source
to share
This is how I solved it using a subquery. But use the answer given by Ajay2707 and marc_s above. It gives the same result. I've put my answer just for reference (subquery) here :)
SELECT cities.name AS city, cities.lon, cities.lat, (SELECT countries.name AS country FROM countries WHERE countries.country_id=cities.country_id ) AS country FROM cities
0
Sazal
source
to share
SELECT
ct.name, ct.lat, ct.lon, cnt.name
FROM
cities ct, countries cnt
WHERE
ct.country_id = cnt.country_id
[Using Join Keyword]
SELECT ct.name, ct.lat, ct.lon, cnt.name
FROM cities ct
INNER JOIN countries cnt
ON ct.country_id = cnt.country_id;
-1
Saswat
source
to share
select
a.name, a.country_id, b.cities, b.city_id, b.lat, b.lon, b.name
from
cities b, countries a
where
b.country_id = a.country_id
-2
Paresh gami
source
to share