Re: SQL:Joining same table more than once

From: TillKahle <tillkahle_at_aol.com>
Date: 1995/12/08
Message-ID: <4a9ghi$og2_at_newsbf02.news.aol.com>#1/1

Hi Cathy!

Your statement is:
> select e.id, e.code1, a.description, e.code2, b.description,
> e.code3, c.description
> from emp e, code_table a, code_table b, code_table c
> where e.id = 123
> and a.code = e.code1

>        and (b.code = e.code2 or e.code2 is null)


>	and (c.code = e.code3 or e.code3 is null);

Joining the same table x times is possible. The problem is your join:   "or e.code2 is null" - this does not give a correct join. What you want is probably an outer join, in words:   "give me all code descriptions for e-rows if there is a code at all".

This is done by:

select e.id, e.code1, a.description, e.code2, b.description,

       e.code3, c.description
from emp e, code_table a, code_table b, code_table c where e.id = 123

and a.code(+) = e.code1
and b.code(+) = e.code2
and c.code(+) = e.code3 

Hope this helps!

Till Kahle, Hamburg Received on Fri Dec 08 1995 - 00:00:00 CET