How do you perform an outer join on the results of 2 tables inner joined, without using a subselect?
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?
This was first published in February 2008