We help IT Professionals succeed at work.
Get Started

Need to solve an Oracle Query 3 tables (Joins)

kalees
kalees asked
on
64 Views
Last Modified: 2015-10-22
I am very new to Oracle and SQL and I have the following scenario which I need some assistance in solving.

Tables

AR_Invoice
AR_inv_no      GL_Code     AR_Amount      AR_Date
1234                11500         500.00               01/01/2015
2345                11500         300.00               01/02/2015
3456                11501         250.00               03/01/2015
etc



AP_Invoice
AP_Inv_No      GL_Code    AP_Amount      AP_Date
4567                12500         350.00               02/05/2015
5678                12500         275.00               03/04/2015
6789                12501         150.00               01/03/2015
etc


Tran_Doc_No
Inv_No     GL_Code     Reg_No      Type
1234         11500         ABC 122       M
1234         11500         ABC 122       F
1234         11500         ABC 122       R
2345         11500         ABC 124       F
3456         11501         ABC 121       M
3456         11501         ABC 121       F
3456         11501         ABC 121       R
4567         12500         ABC 122       M
5678         12500         ABC 124       M
5678         12500         ABC 124       R      
6789         12501         ABC 127       M

Most of the records in the Tran_Doc_No have multiple lines, there is much more info which does differ line by line but the only info I need from this table is the Reg_No

The only GL_Codes that I need in the query are 11500 and 12500

I have looked to I need to achieve the following

Reg_No       AR_Inv_No     AR_Amount     AR_Date           AP_Inv_No      AP_Amount      AP_Date
ABC 122        1234             500.00            01/01/2015       4567                350.00               02/05/2015
ABC 124        2345             300.00            01/02/2015       5678                275.00               03/04/2015


I do hope that I have explained this accurate enough, if not please ask.

Kind regards,

Keith
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE