Using CASE Statements in LEFT OUTER JOIN in SQL

Question

I've got a scenario where I want to switch on two different tables in an outer join. It goes something like this:-

         select mytable.id, 
                yourtable.id
           from mytable
left outer join (case
                    when mytable.id = 2 then table2 
                      yourtable on table1.id = table2.id
                    else
                      table3 yourtable on table1.id = table3.id
                 end)

...but it doesn't work. Any suggestions?

Answer 1

Use (Oracle 9i+):

   SELECT mt.id, 
          COALESCE(yt1.id, yt2.id)
     FROM MYTABLE mt
LEFT JOIN YOURTABLE yt1 ON yt1.id = mt.id
                       AND yt.id = 2
LEFT JOIN YOURTABLE yt2 ON yt2.id = mt.id