Subject Re: [firebird-support] outer join help
Author Helen Borrie
At 08:42 AM 26/04/2007, you wrote:
>This is pretty simple, I seldom use outer joins. I need two levels of
>outer joins:
> Table1
> + Table2 [outer]
> + Table3 [outer]
>but I can not get the second one to work. My SQL is below:
> select ...
> from Table1 T1 left join Table2 on T1.Table1_ID = Table2.Table1_ID,
> Table2 outer join Table3 T3 on Table2.Table2_ID = T3.Table2_ID
> where ...
>I've tried various forms of it, some which lock up my application.
>What is the SQL?

Not like that! :-)

You are trying to mix up the obsolete implicit join syntax (which
doesn't apply to outer joins AT ALL) in with the outer joins.

Also, there is no such syntax as "outer join": there are left
[outer] join, right [outer] join and full [outer] join. The "outer"
keyword is optional; the other one isn't.

Lastly, you can't mix table identifiers and aliases in a statement in
Fb 2 and up; so it's a bad idea to do it at all if you're not
planning to retire soon. :-)

Assuming here you want a full join to T3 (just guessing; it might
not be what you want..)

select ... -- make SURE you qualify all columns correctly
from Table1 T1
left join Table2 T2
on T1.Table1_ID = T2.Table1_ID
full join Table3 T3
on T2.Table2_ID = T3.Table2_ID
where ... -- make SURE you qualify *everything* correctly
-- in the where, order by and group by clauses.

Tip: unless you have to save white space, it is a REALLY good idea
to lay out your multi-table statements in a way that makes the
linkages obvious. Even if you don't care, you *might* retire some
day and bequeath your code to someone else. :-)