Home » SQL & PL/SQL » SQL & PL/SQL » outer join problem
outer join problem [message #220298] Tue, 20 February 2007 02:29 Go to next message
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
2736
2683
2684
1104
3978
4854
5096
5269
5400
5424


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
Re: outer join problem [message #220300 is a reply to message #220298] Tue, 20 February 2007 02:39 Go to previous messageGo to next message
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
Re: outer join problem [message #220488 is a reply to message #220300] Tue, 20 February 2007 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: outer join problem [message #220517 is a reply to message #220298] Wed, 21 February 2007 01:30 Go to previous messageGo to next message
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

Re: outer join problem [message #220523 is a reply to message #220298] Wed, 21 February 2007 01:41 Go to previous message
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    


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


Current Time: Wed Dec 07 22:32:12 CST 2016

Total time taken to generate the page: 0.09657 seconds