Relating Three Tables Using Multiple Conditions
Posted on 2007-09-30
I am using SQL Developer to query into an Oracle database. The table structure and the names of the fields is shown below:
The problem I have is there are two keys in the parent table that pull different sets of data from the first child table. For example, DataA is needed from the CHILD_TABLE_1 table by relation to the OrderNumber01 reference from the PARENT_TABLE. In the same query, DataB is needed from the CHILD_TABLE_1 table by relation to the OrderNumber02 reference from the PARENT_TABLE. Finally, DataD is needed from the CHILD_TABLE_2 table by relation to DataD from the CHILD_TABLE_2 that is joined to OrderNumber02 from CHILD_TABLE_1.
All in one SELECT statement, if possible.
I successfully created a query that pulls DataA and DataB from CHILD_TABLE_1 based on the relationships of OrderNumber01 and OrderNumber02 from the PARENT_TABLE. However, I do not know how to pull my DataD from the CHILD_TABLE_2 that depends on the following relationships:
PARENT_TABLE.OrderNumber02 = CHILD_TABLE_1.OrderNumber02
CHILD_TABLE_1.DataB = CHILD_TABLE_2.DataB
I do not have write access to our Oracle db and cannot write PL/SQL scripts.
The query I used so far is as follows:
SELECT a.Key01, b.DataA, b.DataB
FROM parent_table a, parent_table b, child_table_1 c, child_table_2 d
WHERE us.serial_number = a.unit_id and
a.ordernumber01 = c.ordernumber01 and
b.ordernumber02 = d.ordernumber02 and
a.key01 = b.key02
For anyone reading this, I realize you may need a better explanation of this issue. However, any advice is greatly appreciated. Please let me know if I can clarify any of the query requirements.