Need to solve an Oracle Query 3 tables (Joins)

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
kaleesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try this:
/*
drop table myAR_Invoice purge;
create table myAR_Invoice( AR_inv_no number, GL_Code number,  AR_Amount number,  AR_Date date);

insert into myAR_Invoice values(1234,11500,500.00,to_date('01/01/2015','MM/DD/YYYY'));
insert into myAR_Invoice values(2345,11500,300.00,to_date('01/02/2015','MM/DD/YYYY'));
insert into myAR_Invoice values(3456,11501,250.00,to_date('03/01/2015','MM/DD/YYYY'));
commit;

drop table myAP_Invoice purge;
create table myAP_Invoice (AP_Inv_No number, GL_Code number, AP_Amount number, AP_Date date);
insert into myAP_Invoice values(4567,12500,350.00,to_date('02/05/2015','MM/DD/YYYY'));
insert into myAP_Invoice values(5678,12500,275.00,to_date('03/04/2015','MM/DD/YYYY'));
insert into myAP_Invoice values(6789,12501,150.00,to_date('01/03/2015','MM/DD/YYYY'));
commit;


drop table myTran_Doc_No purge;
create table myTran_Doc_No(Inv_No number, GL_Code number, Reg_No char(7), Type char(1));
insert into myTran_Doc_No values(1234,11500,'ABC 122','M');
insert into myTran_Doc_No values(1234,11500,'ABC 122','F');
insert into myTran_Doc_No values(1234,11500,'ABC 122','R');
insert into myTran_Doc_No values(2345,11500,'ABC 124','F');
insert into myTran_Doc_No values(3456,11501,'ABC 121','M');
insert into myTran_Doc_No values(3456,11501,'ABC 121','F');
insert into myTran_Doc_No values(3456,11501,'ABC 121','R');
insert into myTran_Doc_No values(4567,12500,'ABC 122','M');
insert into myTran_Doc_No values(5678,12500,'ABC 124','M');
insert into myTran_Doc_No values(5678,12500,'ABC 124','R');
insert into myTran_Doc_No values(6789,12501,'ABC 127','M');
commit;
*/


select t.reg_no,
	max(r.ar_inv_no), max(r.ar_amount), max(r.ar_date),
	max(p.ap_inv_no), max(p.ap_amount), max(p.ap_date)
from (
		select distinct inv_no, gl_code, reg_no from myTran_Doc_No
	) t
	left outer join myAR_Invoice r on t.inv_no = r.ar_inv_no and t.gl_code = r.gl_code
	left outer join myAP_Invoice p on t.inv_no = p.ap_inv_no and t.gl_code = p.gl_code
where t.gl_code in (11500, 12500)
group by t.reg_no
/

Open in new window

kaleesAuthor Commented:
slightwv, you have come to my rescue again, however, I have provided bad information!!!!

Because I am mocking up the scenario due to data protection, I have give slightly incorrect information, the date sits in Tran_Doc_No, not AP_INVOICE and AR_INVOICE.


I have commented out the date and amended names and it works perfect.

AR_Invoice
AR_inv_no      GL_Code     AR_Amount  
1234                11500         500.00              
2345                11500         300.00              
3456                11501         250.00            
etc



AP_Invoice
AP_Inv_No      GL_Code    AP_Amount    
4567                12500         350.00              
5678                12500         275.00              
6789                12501         150.00              
etc


Tran_Doc_No
Inv_No     GL_Code     Reg_No      Type     DATE
1234         11500         ABC 122       M        01/01/2015
1234         11500         ABC 122       F         01/01/2015
1234         11500         ABC 122       R         01/01/2015
2345         11500         ABC 124       F         01/02/2015
3456         11501         ABC 121       M        03/01/2015
3456         11501         ABC 121       F         03/01/2015
3456         11501         ABC 121       R         03/01/2015
4567         12500         ABC 122       M        02/05/2015
5678         12500         ABC 124       M        03/04/2015
5678         12500         ABC 124       R         03/04/2015
6789         12501         ABC 127       M       01/03/2015

I have attempted to amend your code without success.

Could you again please assist?

Again, appplogies for the dud info

Keith
slightwv (䄆 Netminder) Commented:
Try this:
/*
drop table myAR_Invoice purge;
create table myAR_Invoice( AR_inv_no number, GL_Code number,  AR_Amount number);

insert into myAR_Invoice values(1234,11500,500.00); insert into myAR_Invoice values(2345,11500,300.00); insert into myAR_Invoice values(3456,11501,250.00); commit;

drop table myAP_Invoice purge;
create table myAP_Invoice (AP_Inv_No number, GL_Code number, AP_Amount number); insert into myAP_Invoice values(4567,12500,350.00); insert into myAP_Invoice values(5678,12500,275.00); insert into myAP_Invoice values(6789,12501,150.00); commit;


drop table myTran_Doc_No purge;
create table myTran_Doc_No(Inv_No number, GL_Code number, Reg_No char(7), Type char(1), some_date date); insert into myTran_Doc_No values(1234,11500,'ABC 122','M',to_date('01/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(1234,11500,'ABC 122','F',to_date('01/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(1234,11500,'ABC 122','R',to_date('01/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(2345,11500,'ABC 124','F',to_date('01/02/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(3456,11501,'ABC 121','M',to_date('03/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(3456,11501,'ABC 121','F',to_date('03/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(3456,11501,'ABC 121','R',to_date('03/01/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(4567,12500,'ABC 122','M',to_date('02/05/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(5678,12500,'ABC 124','M',to_date('03/04/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(5678,12500,'ABC 124','R',to_date('03/04/2015','MM/DD/YYYY'));
insert into myTran_Doc_No values(6789,12501,'ABC 127','M',to_date('01/02/2015','MM/DD/YYYY'));
commit;
*/


select t.reg_no,
	max(r.ar_inv_no), max(r.ar_amount), max(case when ar_inv_no is not null then t.some_date end),
	max(p.ap_inv_no), max(p.ap_amount), max(case when ap_inv_no is not null then t.some_date end) from (
		select distinct inv_no, gl_code, reg_no, some_date from myTran_Doc_No
	) t
	left outer join myAR_Invoice r on t.inv_no = r.ar_inv_no and t.gl_code = r.gl_code
	left outer join myAP_Invoice p on t.inv_no = p.ap_inv_no and t.gl_code = p.gl_code where t.gl_code in (11500, 12500) group by t.reg_no
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kaleesAuthor Commented:
Many thanks again  slightwv, sorry for the dud information.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.