OraFAQ Forum: SQL & PL/SQL » outer join problem

Home » SQL & PL/SQL » SQL & PL/SQL » outer join problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

outer join problem [message #220298]

Tue, 20 February 2007 02:29

emadbsb
Messages: 334
Registered: May 2005
Location: egypt

Senior Member

Hii all

i have a problem in my query

lets see this query

select no from givxxxxxx
where gcu_code ='HNLP002';

this query results

there is another table that i want to add it to this query
that contains some of this records
when i match them together it gives me only the records that are found in both tables

see this query

 SELECT GIVXXXXXX.NO  ,SSIVDTVA.CODE
 FROM SSIVDTVA, GIVXXXXXX
 WHERE GIVXXXXXX.NO = SSIVDTVA.GIV_NO (+)
    AND GCU_CODE ='HNLP002'

This results

     NO CODE  
------- ------
   2684 01    
   5424 01    
   4854 01    
   5096 01

The result of the matching records between them
although i used outer join in my condition

i want the result to be like that

     NO CODE  
------- ------
2736   
2683
2684 01    
5096 01    
1104
4854 01    
3978
5269
5400
5424 01

how can i do that

Report message to a moderator

Re: outer join problem [message #220300 is a reply to message #220298]

Tue, 20 February 2007 02:39

pavuluri
Messages: 247
Registered: January 2007

Senior Member

SELECT a.empno,a.ename,b.ename Manager FROM emp a,emp b WHERE a.mgr = b.empno(+)

SELECT a.empno,a.ename,b.ename Manager FROM emp a,emp b WHERE a.mgr = b.empno

see this querys.

u can use self and outter join with same query
u's problem solved
Thanks,
Srinivas

Report message to a moderator

Re: outer join problem [message #220488 is a reply to message #220300]

Tue, 20 February 2007 20:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

SELECT GIVXXXXXX.NO  ,SSIVDTVA.CODE
 FROM SSIVDTVA, GIVXXXXXX
 WHERE GIVXXXXXX.NO = SSIVDTVA.GIV_NO (+)
    AND GCU_CODE (+) ='HNLP002'

If the GCU_CODE column is in SSIVDTVA, then you need to outer join the constant comparison as well.

Ross Leishman

Report message to a moderator

Re: outer join problem [message #220517 is a reply to message #220298]

Wed, 21 February 2007 01:30

emadbsb
Messages: 334
Registered: May 2005
Location: egypt

Senior Member

thanks for your reply

but GCU_CODE IS NOT IN SSIVDTVA

and your query doesnot result what i require

Report message to a moderator

Re: outer join problem [message #220523 is a reply to message #220298]

Wed, 21 February 2007 01:41

emadbsb
Messages: 334
Registered: May 2005
Location: egypt

Senior Member

I solved it
the solution is by using union all

see the solution

 SELECT GIVXXXXXX.NO  ,SSIVDTVA.CODE
  FROM SSIVDTVA, GIVXXXXXX
  WHERE GIVXXXXXX.NO = SSIVDTVA.GIV_NO
AND GCU_CODE='HNLP002'
AND SSGDT_CODE = 106
UNION ALL
SELECT GIVXXXXXX.NO , NULL
FROM GIVXXXXXX
WHERE GCU_CODE='HNLP002'

the result is

     NO CODE  
------- ------
2736   
2683
2684 01    
5096 01    
1104
4854 01    
3978
5269
5400
5424 01

Report message to a moderator

Previous Topic:	Encryption in Oracle 9i Database
Next Topic:	What ESTAMTED do ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 23:42:20 CST 2024