Need Result Set From Two Tables

[bkno]

I have a report request that needs to show exactly what is seen on the front end (Empower). I’m having difficulty creating a query that will give me the result to satisfy the requester’s needs. The tables Fees and Disbursement sit on a Oracle Database and the .Primary key is the Number field. I’ve tried joining the tables on the Number Field which results in duplicates line items. As you can see some of the Disbursement line items are represented in the Fees table by a Hud number and some aren’t. I need to see all of the line items in Disbursement table that are represented by a Hud Number (in the Fees Table) and all of the line items in the disbursement table that aren’t represented by a Hud number. With a result set like the one I have shown below.

Fees Table
Num IDX HUD FeeType FeeAmt FeePaidTo DistTypeCheck#
6567 4 803 F S Fee 600 Appr 0
6567 2 1106 N Fee 200 North 4 1106
Disbursement Table
Disbursement Table
Num IDX TransType DTyp DPayTo DAmt DDate DChk DPayAcct
6657 1 wire 3 Reimburse 600 6/30/05
6657 2 wire 1 North 200 6/29/05
6657 8 chk 3 N Fed 1663 6/28/05
6657 7 wire 3 GE 1920 6/26/05

Combination Result Set Needed
Num Hud Fee AcctPaid Amt Date Check#
6657 803 600 Rienberuse 6/30/05
6657 1106 200 North 6/29/05
6657 1663 N Fed 6/28/05 1106
6657 1920 GE 6/26/05

 

[SantaMufasa]

Bkno,

What data are you joining on between the two tables? I cannot see anything that matches.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bkno]

That seems to be the problem. Other than the number field, there are no matching fields. Is there a way to accomplish my task thru a subquery?

 

[SantaMufasa]

Bkno,

Yes, you can do a subquery and match up non-matching records just as well as you can match non-matching records in a reqular query.

If you cannot tell me how to match records in two different tables, how can we expect Oracle to match them up?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.