Home » SQL & PL/SQL » SQL & PL/SQL » outerjoin
outerjoin [message #364899] Wed, 10 December 2008 07:20 Go to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
Hi,
i have a query which gets keys from a tmp table
eg:
select a,b,c from table1,table2 where table1.key (+) = table2.key
and table1.number =1223 and table2.status= 'abc'
and table1.key in (select key from tmp_table)

now i need to select the values which are in tmp_table
and not matching the above conditions.

whole in ,i have to use outer join with IN condition.

is it possible?if no is there any alternate.

please help if you know how it can be done.

Thank you.
Re: outerjoin [message #364906 is a reply to message #364899] Wed, 10 December 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: outerjoin [message #364910 is a reply to message #364899] Wed, 10 December 2008 07:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select a,b,c from table1,table2 where table1.key (+) = table2.key
and table1.number =1223 and table2.status= 'abc'
and table1.key in (select key from tmp_table)


The Outer join to table2 is pointless - you look for table2.status= 'abc', which means you won't get any outer join rows returned.

Something like this should do
SELECT key
FROM   tmp_table
WHERE  key not in (SELECT table1.key
                   from   table1
                         ,table2 
                   where  table1.key = table2.key
                   and    table1.number =1223 
                   and    table2.status= 'abc')
Previous Topic: Oracle Connection Error
Next Topic: How to create Text file on local machine
Goto Forum:
  


Current Time: Thu Dec 08 00:14:33 CST 2016

Total time taken to generate the page: 0.10349 seconds