a full join on multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

a full join on multiple tables

Author

Topic

matdoya
Starting Member

2 Posts

Posted - 2009-11-30 : 11:45:36

Dear all,

I have the following situation:

I want to do, basically this:

select one.name, b.teamid, a.teamid, b.all_b/100 as b, b.all_f/100 as f, a.actual/100 as a
from
table1 one
full join
(
select two1.r, three1.t, sum(bug1.b) as all_b, sum(bug1.f) as all_f
from
table2 two1,
table3 three1,
table4 four1
where
(some conditions)
and three1.departmentid = two1.departmentid
group by three1.r, four1.t
) as b
on one.requestid = b.requestid
full join
(
select req2.requestid, team2.teamid, sum(bug2.actual) as actual
from
table2 bug2,
table3 req2,
table4 team2
where
two2.requestid = three2.requestid
and four2.departmentid = two2.departmentid
group by three2.r, four2.teamid
)as a
on one.requestid=a.requestid and a.teamid = b.teamid

but the problem is that the result contains null values in the one.name field because the second full join doesn't joins the names from the table1 table where there are no matches for the one.requestid = b.requestid...

What I'm trying to achieve is roughly this:

resulttable1:

key1 key2 data1
key1 key3 data2

resulttable2:

key1 key2 data3
key1 key4 data4

should give the result

key1 key2 data1 data3
key1 key3 data2 null
key1 key4 null data4

how can I achieve this?

best regards,

S.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-30 : 12:46:02

select a.key1, a.key2, b.data1, c.data2
from mytable1 a
left join mytable2 b on a.id = b.id and b.x = "y"
left join mytable3 c on a.id = c.id and c.z = "u"

the above style of code is what you want to follow. change the sample column names and value to your local scenario.

basically..
for each record on my main table (a), get some data from table (b) if available and get some other data from table (c) if available.

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 13:01:28

or use union to join two subqueries and take left join with first

matdoya
Starting Member

2 Posts

Posted - 2009-12-01 : 04:30:47

quote:
Originally posted by AndrewMurphy

select a.key1, a.key2, b.data1, c.data2
from mytable1 a
left join mytable2 b on a.id = b.id and b.x = "y"
left join mytable3 c on a.id = c.id and c.z = "u"

the above style of code is what you want to follow. change the sample column names and value to your local scenario.

basically..
for each record on my main table (a), get some data from table (b) if available and get some other data from table (c) if available.

end

unfortunately key1 and key2 are from a different table, hence my problem, let me clarify:

resulttable1:

a.key1 b.key2 c.data1
a.key1 b.key3 c.data2

resulttable2:

a.key1 b.key2 d.data3
a.key1 b.key4 d.data4

should give the result

a.key1 b.key2 c.data1 d.data3
a.key1 b.key3 c.data2 null
a.key1 b.key4 null d.data4
the problem, really, is that the keys are from different tables...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-01 : 07:10:22

then the "union" o "union all" clause is the way to go...as long as you ahve the same data types involved in all columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources