Relating Three Tables Using Multiple Conditions

I am using SQL Developer to query into an Oracle database. The table structure and the names of the fields  is shown below:

PARENT_TABLE
Key01
OrderNumber01
OrderNumber02

CHILD_TABLE_1
OrderNumber01
OrderNumber02
DataA
DataB

CHILD_TABLE_2
DataB
DataD

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.

Best Regards,
Samuel
samg3Asked:
Who is Participating?
 
Yuval_ShohatCommented:
assuming you need it all in one select and one row:

select PT.Key01, PT.OrderNumber01, PT.OrderNumber02, CT1.DataA, CT2.DataB, CT3.DataD
from PARENT_TABLE as PT left join CHILD_TABLE_1 as CT1 on PT.OrderNumber01=CT1.OrderNumber01
left join  CHILD_TABLE_1 as CT2 on PT.OrderNumber02=CT1.OrderNumber02
left join CHILD_TABLE_2 as CT3 on CT2.DataB=CT3.DataB
where us.serial_number = a.unit_id...

the idea is to join everything according to your parent table.

would this work?

-=Yuval=-
0
 
JimBrandleyCommented:
Do you want different rows for the two results (order1 and order2) or one row with all four values for each key?

Jim
0
 
samg3Author Commented:
Yuval, It worked perfectly. Thank you for your immediate, simple, and correct response. It will be extremely appreciated by many here at our company.

Jim, sorry for not clarifying that all four values are in one row.

Thank you both for your help.
Samuel
0
 
samg3Author Commented:
I forgot to mention thatthe second left join table name was changed from CT1OrderNumber02  to CT2.OrderNumber02, as follows:

select PT.Key01, PT.OrderNumber01, PT.OrderNumber02, CT1.DataA, CT2.DataB, CT3.DataD
from PARENT_TABLE as PT left join CHILD_TABLE_1 as CT1 on PT.OrderNumber01=CT1.OrderNumber01
left join  CHILD_TABLE_1 as CT2 on PT.OrderNumber02=CT2.OrderNumber02
left join CHILD_TABLE_2 as CT3 on CT2.DataB=CT3.DataB
where us.serial_number = a.unit_id...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.