Home » SQL & PL/SQL » SQL & PL/SQL » Select Records Whether or Not They Exist In Another Table (Windows 2000 Server, Oracle 9iR2)
Select Records Whether or Not They Exist In Another Table [message #289287] Thu, 20 December 2007 09:54 Go to next message
deay
Messages: 51
Registered: August 2005
Member
Hello

I have a report I need to generate that produces open pledges.
the report needs to show those pledges with no pledgepay records along with those that do have a pledgepay record if they made a partial payment towards that pledge.

example:
ID      	NAME	FUND	PLEDGECODE	PLEDGEDATE	PLEDGE	PAYDATE	PLEDGEPAY	BALANCE
2602	L. Jack Spence	DV	DVA0604	6/21/2006	35.00	      9/7/2006	25.00	        10.00
1696    Bryant Smith    DV      DVA0702 6/30/2006      100.00				        100.00


when I run the below sql statement only id# 2602 results for obvious reasons there is a pledgepay record being joined..how do I get the donor with no pledgepay record(1696) to generate as well.

(this is just a test script I am running)

SELECT d.id, d.First||' '||d.last Name, g.fund, 
       g.pledgecode, g.pledgedate, g.pledgeamt, pa.paydate,pa.pledgepay
    FROM donor d, pledge g , pledgepay pa
    WHERE d.id = g.id
    AND pa.id=d.id
    AND g.type = 'P'
    AND g.balance > 0
    AND pa.ltrans=g.trans
    AND d.id in(1696,2602)



thanks for any help/tips.
(sorry I can't seem to get the record alignment correct in the above example)
Re: Select Records Whether or Not They Exist In Another Table [message #289289 is a reply to message #289287] Thu, 20 December 2007 10:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You are looking for an Outer Join.

This assumes you want all the records from g whether or not there is a corresponding pa record, put the (+) on the other side if the logic should be the opposite way,
    AND pa.ltrans(+)=g.trans

Re: Select Records Whether or Not They Exist In Another Table [message #289290 is a reply to message #289287] Thu, 20 December 2007 10:12 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
Marcl
when I try that the entire system just hangs.
Re: Select Records Whether or Not They Exist In Another Table [message #289293 is a reply to message #289290] Thu, 20 December 2007 10:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In Oracle 9i you can use ANSI joins, which are more readable in my opinion than the old oracle join syntax. try :

.....
FROM donor d
  join pledge g 
    on g.id      = d.id
   and g.type    = 'P'
   and g.balance > 0
  left outer join pledgepay pa
    on pa.id     = g.id
   AND pa.ltrans = g.trans
 WHERE d.id in(1696,2602)
icon14.gif  Re: Select Records Whether or Not They Exist In Another Table [message #289295 is a reply to message #289287] Thu, 20 December 2007 10:30 Go to previous message
deay
Messages: 51
Registered: August 2005
Member
ThomasG

PERFECT!...exactly what I needed.
Previous Topic: simple select with ORA-00904
Next Topic: Schedule DBMS job every day in week except Friday
Goto Forum:
  


Current Time: Tue Dec 06 08:45:12 CST 2016

Total time taken to generate the page: 0.12658 seconds