Clever Geek Handbook

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


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


source


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


source


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


source 


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


source 


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


source






More articles:


All Articles