?
Solved

Relating Three Tables Using Multiple Conditions

Posted on 2007-09-30
4
Medium Priority
?
845 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:samg3
  • 2
4 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19988018
Do you want different rows for the two results (order1 and order2) or one row with all four values for each key?

Jim
0
 
LVL 8

Accepted Solution

by:
Yuval_Shohat earned 2000 total points
ID: 19988037
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
 

Author Comment

by:samg3
ID: 19988106
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
 

Author Comment

by:samg3
ID: 19988347
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This video shows how to recover a database from a user managed backup
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question