Union query.. I guess:)
-
SELECT * FROM CITY_POPULATION; CITY_ID CITY_POPULATION ------- --------------- 1 100 2 200 3 300 SELECT * FROM CITY_INFO; CITY_ID CITY_NAME ------- --------- 1 FIRST CITY 2 SECOND CITY 3 THIRD CITY How can I have some sort of union query that would have the city id, name and population in one output where the id columns from the two tables match, I mean, the city names from the second table should be picked up to match their id number from the other table showing how much population is in that country id with it's name.. something like: ID, Country Name, Population Many thanks guys!
modified on Wednesday, September 3, 2008 3:30 PM
-
SELECT * FROM CITY_POPULATION; CITY_ID CITY_POPULATION ------- --------------- 1 100 2 200 3 300 SELECT * FROM CITY_INFO; CITY_ID CITY_NAME ------- --------- 1 FIRST CITY 2 SECOND CITY 3 THIRD CITY How can I have some sort of union query that would have the city id, name and population in one output where the id columns from the two tables match, I mean, the city names from the second table should be picked up to match their id number from the other table showing how much population is in that country id with it's name.. something like: ID, Country Name, Population Many thanks guys!
modified on Wednesday, September 3, 2008 3:30 PM
You would use a join for this. Select * From City_Info i Inner Join City_Population p on p.City_Id = i.City_Id The output should be similar to:
City_Id City_Name City_Id City_Population
1 First City 1 100
2 Second City 2 200
3 Third City 3 300You probably wouldn't want City_Id two times, so you would need to specify the columns that you want in the select list. Be sure to qualify the column names so you don't get an ambiguous column error (ex:
i.City_id, i.City_Name, p.City_Population). -
SELECT * FROM CITY_POPULATION; CITY_ID CITY_POPULATION ------- --------------- 1 100 2 200 3 300 SELECT * FROM CITY_INFO; CITY_ID CITY_NAME ------- --------- 1 FIRST CITY 2 SECOND CITY 3 THIRD CITY How can I have some sort of union query that would have the city id, name and population in one output where the id columns from the two tables match, I mean, the city names from the second table should be picked up to match their id number from the other table showing how much population is in that country id with it's name.. something like: ID, Country Name, Population Many thanks guys!
modified on Wednesday, September 3, 2008 3:30 PM
-
You would use a join for this. Select * From City_Info i Inner Join City_Population p on p.City_Id = i.City_Id The output should be similar to:
City_Id City_Name City_Id City_Population
1 First City 1 100
2 Second City 2 200
3 Third City 3 300You probably wouldn't want City_Id two times, so you would need to specify the columns that you want in the select list. Be sure to qualify the column names so you don't get an ambiguous column error (ex:
i.City_id, i.City_Name, p.City_Population). -
Do you really want to union or perhaps join? Do you want output like :
City_Id City_Population CityName
1 100 First City
2 200 Second City...or something else?
-
Mika Wendelius wrote:
union or perhaps join?
:-O Thanks for the hint mate!
All generalizations are wrong, including this one! (\ /) (O.o) (><)
-
You would use a join for this. Select * From City_Info i Inner Join City_Population p on p.City_Id = i.City_Id The output should be similar to:
City_Id City_Name City_Id City_Population
1 First City 1 100
2 Second City 2 200
3 Third City 3 300You probably wouldn't want City_Id two times, so you would need to specify the columns that you want in the select list. Be sure to qualify the column names so you don't get an ambiguous column error (ex:
i.City_id, i.City_Name, p.City_Population).Sorry Scott, but I still have a question though Using your inner join statement, why do I get the same value from any column of the second table, like population here from city population would be 100 for the whole column:confused: Please help me Mr. "Scott Tiger ;)"
All generalizations are wrong, including this one! (\ /) (O.o) (><)