3 tables left outer join

iccsi schreef op 07-10-2011 17:15:
> I have 3 tables and would like to have all data exists on table 1 even
> does not exist in table 2 and tbale 3 are there any way to have left
> outer join for 3 tables or I have to use 2 quries to complete?
>
> Your information is great appreciated,
>
> Here is table example data:
>
> Table1
>
> MyKey, My Data
> 1 MyData1
> 2 MyData2
> 3 MyData3
> 4 MyData4
>
>
> Table2
> MyKey, My Data
> 1 MyData5
>
>
> Table3
> MyKey, MyData
> 2 MyData6
>
> I would like to have a result which include all MyKey.
> I tried to use one query, but I can not get all records.

Not sure what you mean by this.
Can you post 'Table4' with columns MyKey/MyData with the desired results
please?
 >> Stay informed about: 3 tables left outer join 

iccsi schreef op 07-10-2011 17:15:
> I have 3 tables and would like to have all data exists on table 1 even
> does not exist in table 2 and tbale 3 are there any way to have left
> outer join for 3 tables or I have to use 2 quries to complete?
>
> Your information is great appreciated,
>
> Here is table example data:
>
> Table1
>
> MyKey, My Data
> 1 MyData1
> 2 MyData2
> 3 MyData3
> 4 MyData4
>
>
> Table2
> MyKey, My Data
> 1 MyData5
>
>
> Table3
> MyKey, MyData
> 2 MyData6
>
> I would like to have a result which include all MyKey.
> I tried to use one query, but I can not get all records.
>
> Your help is great appreciated,
>
> iccsi
>

If you want the data from table1 that's not in table2 or table3, based
on MyKey, then this might be what you're looking for. Simply do a UNION
ALL for Table2 and Table3, then do a LEFT JOIN from Table1 and Table2/3:

declare @T1 table(MyKey int, MyData char(15))
declare @T2 table(MyKey int, MyData char(15))
declare @T3 table(MyKey int, MyData char(15))

insert into @t1
select 1, 'mydata1'
union select 2, 'mydata2'
union select 3, 'mydata3'
union select 4, 'mydata4'
insert into @t2
select 1, 'mydata5'
insert into @t3
select 2, 'mydata6'

select
t1.MyKey
from
@t1 a
left join
(select * from @t2 union all select * from @t3) c
on
a.MyKey = c.MyKey
where
c.MyKey is null

Best,
Henk
 >> Stay informed about: 3 tables left outer join