Is this where we use a RIGHT OUTER JOIN?
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
How do you perform an outer join on the results of 2 tables inner joined, without using a subselect?
Subselect example:
select a,b,c
from table t1
outer join
(select t2.create_dt_tm, t3.page_cnt
from t2, t3
where t2.id = t3.t2_id
and t2.create_dt_tm > sysdate -4)
oj on (t1.id = t2.t1_id);
In SQL FAQ: Common SQL questions, part 2 (05 July 2007), towards the end, there is mention of "esoteric" questions which we suspect are university-level homework assignments. This question sounds suspiciously like one of those.
My first reaction would be to say, "You don't." Why would you feel it necessary to do this without using a subselect? Unless it was some kind of trick question.
By the way, there are some serious problems in your question. OUTER JOIN by itself is incorrect. The word OUTER is optional, but you must say whether it's LEFT or RIGHT or FULL. There are also typos in the table aliases. But the intent of your question was clear, assuming you meant a left outer join from t1 to the subselect.
Perhaps this is one of those rare examples of an appropriate use for a RIGHT OUTER JOIN.
select a,b,c
from t3
inner
join t2
on t2.id = t3.t2_id
and t2.create_dt_tm > sysdate -4
right outer
join t1
on t1.id = t2.t1_id
Please do let me know how that works. RIGHT OUTER JOINs give me the creeps. Why didn't you want to use a subselect again?
Oracle White Papers: Fusion Middleware