Question Locked

Oracle 11g - How to retrieve data from multiple related tables

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to create a sql script that uses data that resides in different tables. I need 3-4 different columns from one table and I need one column from another table - all to show up in the same results.

Further details:

Table 1: Shipment_Stop
Columns: Shipment_ID, Stop_Number, Location_ID

Table 2: Location
Columns: Location_Name

The Shipment_Stop table Foreign Keys into the Location table.

My script needs to pull the Shipment ID, Stop Number, Location ID and Location Name and the WHERE clause is 'where shipment_id = 'XXXXX'

How can I get the results for Location Name when that data resides under a different table?

3 replies

by locolobo

and that it is related to Location_ID in table 1. I'm not familiar with Oracle 11 but in 8 or 10 the statement would look like this:

SELECT Shipment_Stop.Shipment_ID,
Shipment_Stop.Stop_Number,
Location.Location_Name
FROM Shipment_Stop, Location
WHERE Shipment_Stop.Location_Id = Location.Location_ID;

If table 2 doesn't have a Location_ID column then how are the tables related?

by csgs

Thanks LocoLobo - this was helpful. I modified your sql a little to match the data in my tables and columns here and now I am able to get the exact data that I need.

The tables are related in that the Location_ID can be found in both the Shipment_Stop table and the Location table. That is the link between the two tables so that I can pull the Location Name from the Location table to include it in the results.

Thanks for your help.

This discussion is closed to new replies.