Join Four Columns To The Same Table

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 2000 Forums

SQL Server Development (2000)

Join Four Columns To The Same Table

Author

Topic

GazNewt
Starting Member

14 Posts

Posted - 2006-06-14 : 11:45:46

I have TableA which contains a description column and four ID columns. These four ID columns each link to different records in TableB. TableB also contains a description column.

I want a select statement for all rows in TableA that will show me the TableA description column as well as the TableB description column for the four IDs from TableA which link to TableB.

I don't think this is a basic join on TableA with TableB because there are four links in TableA to TableB and I want the description for each. I may be wrong!

I'm a sql novice, can anyone help?

Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-14 : 12:09:03

GazNewt - I'm not sure which of these 2 scenarios you're describing, but one of these should help you...

--data
declare @TableA table (c1 int, c2 int, c3 int, c4 int, description varchar(10))
insert @TableA
          select 1, 1, 1, 1, 'aaaa'
union all select 2, 2, 2, 2, 'bbbb'
union all select 1, 2, 3, 4, 'abcd'

declare @TableB table (c1 int, c2 int, c3 int, c4 int, description varchar(10))
insert @TableB
          select 1, 1, 1, 1, 'xxxx'
union all select 2, 2, 2, 2, 'yyyy'
union all select 3, 3, 3, 3, 'zzzz'

--calculation
select a.*, b.description from @TableA a left outer join @TableB b on
    a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3 and a.c4 = b.c4

/*results
c1          c2          c3          c4          description description 
----------- ----------- ----------- ----------- ----------- ----------- 
1           1           1           1           aaaa        xxxx
2           2           2           2           bbbb        yyyy
1           2           3           4           abcd        NULL
*/

-----------------------OR---------------------

--data
declare @TableA table (c1 int, c2 int, c3 int, c4 int, description varchar(10))
insert @TableA
          select 1, 1, 1, 1, 'aaaa'
union all select 2, 2, 2, 2, 'bbbb'
union all select 1, 2, 3, 4, 'abcd'

declare @TableB table (id int, description varchar(10))
insert @TableB
          select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'

--calculation
select a.*, b1.description, b2.description, b3.description, b4.description from @TableA a
    left outer join @TableB b1 on  a.c1 = b1.id
    left outer join @TableB b2 on  a.c2 = b2.id
    left outer join @TableB b3 on  a.c3 = b3.id
    left outer join @TableB b4 on  a.c4 = b4.id

/*results
c1          c2          c3          c4          description description description description description 
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           1           1           1           aaaa        a           a           a           a
2           2           2           2           bbbb        b           b           b           b
1           2           3           4           abcd        a           b           c           NULL
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 12:53:36

Ryan, I think he means that the four id columns in @tableA points to four different rows in @tableB. Also the four description rows for @tableB should be concatenated and presented in one line, together with description from original table @tableA.

Such as

TableA
----------------------------
1  1  2  3    4  This is row 1
2  5  9 19 NULL  This is row 2

TableB
----------------------------
 1  Desc 1
 2  Desc 2
 3  Desc 3
 4  Desc 4
 5  Desc 5
 9  Desc 9
19  Desc 19

Output
------
1  This is row 1  Desc1Desc2Desc3Desc4 
2  This is row 2  Desc5Desc9Desc19

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-14 : 14:03:41

Yes, Peter. I wrote the 1st scenario, then re-read the post and decided the 2nd scenario was 'more likely' and wrote for that! I didn't want to then waste what I'd written

I think the 2nd scenario is roughly aligned with what you're suggesting the requirement might be. I'm not sure about the concatenation though (although obviously that's trivial).

GazNewt - can you clarify your requirement?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

GazNewt
Starting Member

14 Posts

Posted - 2006-06-14 : 16:01:47

Thanks Ryan, the second example is exactly what I was after.

I never thought about doing 4 joins because it seems a bit like overkill but thinking about it, it isn't overkill it's just explicit.

I've got four id columns each of which link to a different record in TableB so they are all separate joins! Of course!

Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-15 : 04:59:28

Thanks for feedback GazNewt - and good luck!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources