IT Employment



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

By csgs ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Assuming Table 2 has the column Location_ID

by LocoLobo In reply to Oracle 11g - How to retri ...

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,
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?

Collapse -

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

by csgs In reply to Assuming Table 2 has the ...

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.

Related Discussions

Related Forums