SQL/Oracle Server, Full Outer Join and 3 or more Tables

Question

I got a little problem.

I'm using Full outer Join in Sql server - so far it works but now I add a table and it doesn't work as it should.

Here is my code:

SELECT *
FROM Table1 h , (db1..Table2 s FULL OUTER JOIN  db1..Table3 k
on k.attributT3_1 = s.attributT2_1 
    and left(k.attributT3_2,4) = year(s.attributT2_2)
    and substring(k.attributT3_2,6,1) = s.attributT2_2
    and (case when k.attributT3_3 = 0 and k.attributT3_4 = 11 then 10 
        when k.attributT3_3 = 0 and k.attributT3_4 = 14 then 40
        when k.attributT3_3 = 0 and k.attributT3_4 = 16 then 60 
        when k.attributT3_3 = 0 and k.attributT3_4 = 90 then 10 
        when k.attributT3_3 = 1 and k.attributT3_4 = 11 then 11 
        when k.attributT3_3 = 2 and k.attributT3_4 = 11 then 12 
        when k.attributT3_3 = 4 and k.attributT3_4 = 11 then 14 
        when k.attributT3_3 = 7 and k.attributT3_4 = 11 then 17 
        else k.attributT3_3 end) = s.attributT2_3) 
where h.attributT1_1 = k.attributT3_1 
and s.attributT3_1 = '  260585'
and h.attributT1_2 = 055

That's my SQL, may be a little bit confusing^^

My problem here is FULL OUTER JOIN doesn't work because I added Table1, because there are some infos I need and now FULL OUTER JOIN works like LEFT OUTER JOIN.

It is also possible that I need to add one or 2 more Tables in this FULL OUTER JOIN.

Does anyone have an idea how I could get it to work? (and yeah I know I could use 2 left outer joins and an union and make it work but that's not what I want to do)

btw the script sould work on oracle too :D

the output should be like this: http://img402.imageshack.us/img402/4618/bildwq.jpg

Column1 = Table1 Column2 = Table2 Column3 = Table3

the null values do not exist on the table

my script does the same except u cant see the rows in column 2 with nulls

Answer 1

The WHERE clause is restricting the results that can be returned:

where h.attributT1_1 = k.attributT3_1 
and s.attributT3_1 = '  260585'
and h.attributT1_2 = 055

This means that only results where tables H, K and S have contributed a row (not nulls) will be shown. Effectively you have removed the OUTER from the join. Try moving those conditions into the join definitions.

An example to show what I mean:

SQL> create table t1 (id integer, text varchar2(10));
SQL> create table t2 (id integer, text varchar2(10));
SQL> insert into t1 values (1, 'text');
SQL> insert into t1 values (2, 'text');
SQL> insert into t2 values (2, 'text');
SQL> insert into t2 values (3, 'text');
SQL> commit;

A full outer join:

SQL> select * from t1 full outer join t2 on t1.id = t2.id;

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text
                               3 text
         1 text

Now with filters in the WHERE clause (note that 2 records disappear just as if the "full outer" wasn't there):

  1* select * from t1 full outer join t2 on t1.id = t2.id
  2* where t1.text='text'
  3  and t2.text='text';

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text

Now with the filters moved into the join:

SQL> select * from t1 full outer join t2 on t1.id = t2.id
  2                                      and t1.text='text'
  3                                      and t2.text='text';

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text
                               3 text
         1 text             

Answer 2

Well i change a little bit my sql script and i works now! thx for helping

greeting Auro