user2082599 user2082599 - 3 months ago 15
SQL Question

Oracle Select from multple Tables

Good Day all,

I am looking for some help. I have multiple Tables which has primary keys and foreign keys linking each other. The Tables are:

Continent
Sub-Continent
Country
Region
City
Location


As example of how the layout is for each:

continent_id | continent_name
1 | Africa
2 | America

sub_continent_id | sub_continent_name | continent_id
A1 | Southern Africa | 1
B1 | Western Africa | 1
A2 | North America | 2
B2 | South America | 2

country_id | country_name | sub_continent_id
CAN | Canada | A2
ZA | South Africa | A1


This continues for the rest, Region, City and location.
As can be seen, the ID's reference another Table. So:

Country_name Canada references sub_continent_id A2 which is North
America. North America reference continent_id 2 which is Americas


What I want to try and do is to run a sql query that if I chose a location, it should return the rest of the values from other tables. So if I select a Location called Triange Building. it will automatically return:

Triangle Building, New York City, New York, North America, Americas


I am uncertain of what query to run to collect all these values each time.

I can run a single query like this to show me region name and city name, but do not know how to select more values from more Tables.

SELECT E.city_name NAME,D.region_name DNAME
FROM CITY E JOIN REGION D
ON (E.region_id = D.region_id);


Thanks a mil in advance, and terribly sorry if this is a silly question.

Answer

Just join all the tables

SELECT E.city_name NAME,D.region_name DNAME, C.COUNTRY_NAME, SC.SUB_CONTINENT_NAME, CO.CONTINENT_NAME
FROM CITY E JOIN REGION D ON E.region_id = D.region_id
            JOIN COUNTRY C ON D.country_id = C.country_id
            JOIN SUB-CONTINENT SC ON C.sub_continent_id = SC.sub_continent_id
            JOIN CONTINENT CO ON SC.continent_id = CO.continent_id;
Comments