CODEWITHSUNDEEP
sql
user640601
user640601

Reputation: 9

I want SQL query to relate two tables data ,where both tables have only one column each

I have two tables Country and City. Both tables have only one column. In country table I have values as INDIA,UK,US and so on. IN City table I have values like LONDON,BANGALORE,DELHI,WASHINGTON and so on.

I want to write SQL query such that output should be like when I select UK ,LONDON should come in front of that ,when US ,WASHINGTON Should appear in US row and so on.

Please provide me the query.

Upvotes: 0

Views: 4616

Answers (4)

Optillect Team
Optillect Team

Reputation: 1747

It is impossible to make relation between these two tables since they have ONLY ONE column. You have to add [Country] column to the [City] table to known what country a city belongs to.

Upvotes: 2

AllisonC
AllisonC

Reputation: 3099

You need to create a foreign key in the City table, then you join the tables together 


ALTER TABLE city ADD COLUMN country VARCHAR(25);
ALTER TABLE city ADD PRIMARY KEY (city);
ALTER TABLE country ADD PRIMARY KEY (country);
ALTER TABLE city ADD FOREIGN KEY (country) REFERENCES country(country)
SELECT CONCAT(city, ", ", country) FROM city JOIN country USING (country)

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

There are several options here.

The first thing to consider is whether you want to use the city/country name as the way of uniquely identifying a row. Although these are very unlikely to change, its not without precidence. Bombay => Mumbai, Ceylon => Sri Lnka, spelling mistakes, etc. Personally I'd recommend having an integer field as the PRIMARY KEY / UNIQUE IDENTIFIER on each table. You can then change the names (including spelling mistakes) without impacting anything else in the database.

You then need to relate the two tables. One option is to add a field to the CITY table that points to a unique identifier in the COUNTRY table. An alternative is to have a completely seperate table of two columns, relating each City's unique identifier to it's associated Country's unique identifier.

Upvotes: 0

b_dubb
b_dubb

Reputation: 421

you need a third table to store the relationships between the two tables and you need to add ID fields to each of your existing tables. 

table city
id | city
1 | chicago

table country
id | country
3 | United States

table lookup
id | city_id | country_id
5 | 1 | 3

you would have to perfom a JOIN to connect the tables

SELECT * FROMlookupJOIN city ON lookup.city_id = city.id JOIN country ON lookup.country_id = country.id

hope this helps

Upvotes: 0

Related Questions