Home » SQL & PL/SQL » SQL & PL/SQL » Single SQL query to join 3 tables (oracle 10g , UNIX)
Single SQL query to join 3 tables [message #380760] Tue, 13 January 2009 04:17 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
I am having 3 tables in my database.

1.application_stg_extn
2.application_fact
3.application_fact_extn

My aim is to write the query which will solve the below purpose

STEP1:
Inner join(matching records) between application_stg_extn and application_fact on a key reference_number and retrive all the fields from application_stg_extn and one field named application_key from
application_fact.

STEP2:
Now having a left outer join between the result in step1 with the table application_fact_extn on the key reference_number.Here we can include rowid
so that we can know if that record is present in the application_fact_extn table.If the row id in the output result is NULL then it is not present.

Please help me writing a 'single query' joining these 3 tables.

i have written the below query.Please let me know if it will work as per the situation I have given above.


SELECT /*+ parallel(t,32) */ 
       ext.rowid as target_rowid
,      t.application_key
,      s.*
FROM   LOADER_SCHEMA.application_stg_extn s
,      OWNER_SCHEMA.application_fact      t
,      OWNER_SCHEMA.application_fact_extn ext
WHERE  s.reference_nbr=t.reference_nbr 
AND    s.reference_nbr=ext.reference_nbr (+)


[Mod-Edit: Frank added code tags and formatted SQL]

[Updated on: Tue, 13 January 2009 04:22] by Moderator

Report message to a moderator

Re: Single SQL query to join 3 tables [message #380765 is a reply to message #380760] Tue, 13 January 2009 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
It looks ok from the information supplied. Have you tried running it?
Re: Single SQL query to join 3 tables [message #380766 is a reply to message #380760] Tue, 13 January 2009 04:52 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
Yes the query is running.i tested with 3-4 records.Dont have much flexibility for testing.

Can you also let mek now if it will work good performance wise
Re: Single SQL query to join 3 tables [message #380777 is a reply to message #380760] Tue, 13 January 2009 05:33 Go to previous message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd need a lot more information to answer that.
Have a read of this:
http://www.orafaq.com/faq/performance_tuning
Previous Topic: Understanding AUTOTRACE output
Next Topic: Swap values of two rows.
Goto Forum:
  


Current Time: Tue Dec 06 04:51:03 CST 2016

Total time taken to generate the page: 0.06162 seconds