MVSFORUMS.com :: View topic - Join

MVSFORUMS.com Forum Index

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Register

Log in to check your private messages

Join

printer-friendly view

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

SK2007
Beginner

Joined: 25 Jan 2007
Posts: 40
Topics: 17

Posted: Wed Oct 05, 2011 2:47 pm Post subject: Join

Reply with quote

Hi,

I have two tables. 1) open table 2) pending table
OpenTable has threecolumns
1) Mbr-id
2) Invoice-no
3) tot-amnt

Code:

----+----1----+----2----+-
**************************
1234567 545875 100.00
1234567 454566 200.00
2343445 565464 150.00

PendingTable has three columns
1) pend-mbr-id
2)pend-invoice-no
3)pend-amnt

Code:

----+----1----+----2----+
*************************
1234567 000000 50.00
1234567 545875 75.00
1234567 745745 300.00
2343445 111111 120.00
2343445 565464 150.00

I need all open invoices, matching invoices and invoices from pendingtable for a given mbr-id
I need below columns in output
invoice-no,tot-amnt,pend-invoice-no,pend-amnt

So, the expected output for mbrid 1234567 is

Code:

----+----1----+----2----+----3---
***************************** Top
000000 50.00
545875 100.00 545875 75.00
454566 200.00
745745 300.00

the output for mbr-id 2343445 should be

Code:

----+----1----+----2----+----3
*****************************
111111 120.00
565464 150.00 565464 150.00

Thanks for your help.
SK2007.

Send private message

kolusu
Site Admin

Site Admin

Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

Posted: Wed Oct 05, 2011 4:29 pm Post subject:

Reply with quote

SK2007,

Try this sql(untested)

Code:

SELECT INVOICE_NO AS INVOICE_NO
,TOT_AMNT AS TOT_AMNT
,PEND_INVOICE_NO AS PEND_INVOICE_NO
,PEND_AMNT AS PEND_AMNT
FROM OPEN_TABLE
,PEND_TABLE
WHERE MBR_ID = 1234567
AND INVOICE_NO = PEND_INVOICE_NO
UNION
SELECT NULLIF(1,1) AS INVOICE_NO
,NULLIF(1,1) AS TOT_AMNT
,PEND_INVOICE_NO AS PEND_INVOICE_NO
,PEND_AMNT AS PEND_AMNT
FROM PEND_TABLE
WHERE (PEND_MBR_ID,PEND_INVOICE_NO) NOT IN (SELECT MBR_ID
,INVOICE_NO
FROM OPEN_TABLE
WHERE MBR_ID =1234567)
AND PEND_MBR_ID = 1234567
UNION
SELECT INVOICE_NO AS INVOICE_NO
,TOT_AMNT AS TOT_AMNT
,NULLIF(1,1) AS PEND_INVOICE_NO
,NULLIF(1,1) AS PEND_AMNT
FROM OPEN_TABLE
WHERE (MBR_ID,INVOICE_NO) NOT IN (SELECT PEND_MBR_ID
,PEND_INVOICE_NO
FROM PEND_TABLE
WHERE PEND_MBR_ID =1234567)
AND MBR_ID = 1234567
;

Kolusu

Send private message

Send e-mail

Visit poster's website

GuyC
Supermod

Joined: 27 May 2011
Posts: 35
Topics: 0

Posted: Thu Oct 06, 2011 8:51 am Post subject:

Reply with quote

or use full outer join , thats why it's there

Code:

SELECT A.INVOICE_NO
,A.TOT_AMNT
,B.PEND_INVOICE_NO
,B.PEND_AMNT
FROM (select * from OPEN_TABLE where mbr_id =1234567) A
full outer join
(select * from PEND_TABLE where pend_mbr_id =1234567) B
on A.INVOICE_NO = B.PEND_INVOICE_NO

Send private message

SK2007
Beginner

Joined: 25 Jan 2007
Posts: 40
Topics: 17

Posted: Mon Oct 10, 2011 9:37 pm Post subject:

Thanks Kolusu and GuyC. It is working as expected...thanks again for your help.

Send private message

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group