Home » SQL & PL/SQL » SQL & PL/SQL » Self Join Using 2 tables (Oracle 10 g)
icon1.gif  Self Join Using 2 tables [message #420203] Thu, 27 August 2009 09:38 Go to next message
veemani
Messages: 10
Registered: July 2009
Location: Chennai
Junior Member
Hi,

I am currently need to modify one of the query which is using self join like given below:

Existing Query:
==============
select col1,col2
from table1 t1
table1 t2
Where
table1.c1=table2.c1 and
table1.c2=table2.c2 and
table1.c3=table2.c3 and
table1.c4=table2.c4;
As per one of my project requirments, one of the column say c2 is moving to a new table.

Can anyone plz. help me, how do I modify this query?

Note: Tables t1 and new table can be joined using key columns.
Re: Self Join Using 2 tables [message #420211 is a reply to message #420203] Thu, 27 August 2009 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add the third table in "from" clause and join condition in "where" one.

Regards
Michel

[Updated on: Thu, 27 August 2009 09:58]

Report message to a moderator

Re: Self Join Using 2 tables [message #420213 is a reply to message #420203] Thu, 27 August 2009 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I am currently need to modify one of the query
include commas between table names in FROM clause
Re: Self Join Using 2 tables [message #420216 is a reply to message #420203] Thu, 27 August 2009 10:08 Go to previous messageGo to next message
veemani
Messages: 10
Registered: July 2009
Location: Chennai
Junior Member
Hi Michel,
Will that be faster?

I am actually looking for an option, which will help me get the output faster as around a million records will be fetched.
Re: Self Join Using 2 tables [message #420218 is a reply to message #420203] Thu, 27 August 2009 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Will that be faster?
post EXPLAIN PLAN for alternative solutions
Re: Self Join Using 2 tables [message #420224 is a reply to message #420216] Thu, 27 August 2009 10:47 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is faster than what?

Regards
Michel
Previous Topic: drop a partitioned table oracle11g
Next Topic: user created with default automatic privilege
Goto Forum:
  


Current Time: Fri Dec 09 02:13:10 CST 2016

Total time taken to generate the page: 0.08296 seconds