Multiple fields in one join - SQL Server Forums

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)

Multiple fields in one join

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2009-01-23 : 06:54:55

Hi,

Imagine I have a table like this ...

Action1ID int
Action1Date datetime
Action2ID int
Action2Date datetime
Action3ID int
Action3Date datetime

And so on ... in fact there's more than ten actions to record in thsi table.

The ID fields all join to an Administrator table which includes a name field, but it's possible for each action to be completed by a different member of staff, so ...

Action1ID = 1
Action2ID = 32
Action3ID = 5

Is there a way I can write a query with a single join to the Administrator table that will pull out all the names in one go? i.e.

Action1ID = 1
name = 'Bob Smith'
Action2ID = 32
name = 'Robert Smith'

etc

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-01-23 : 07:58:57

quote:
Originally posted by mattt

Hi,

Imagine I have a table like this ...

Action1ID int
Action1Date datetime
Action2ID int
Action2Date datetime
Action3ID int
Action3Date datetime

And so on ... in fact there's more than ten actions to record in thsi table.

The ID fields all join to an Administrator table which includes a name field, but it's possible for each action to be completed by a different member of staff, so ...

Action1ID = 1
Action2ID = 32
Action3ID = 5

Is there a way I can write a query with a single join to the Administrator table that will pull out all the names in one go? i.e.

Action1ID = 1
name = 'Bob Smith'
Action2ID = 32
name = 'Robert Smith'

etc

If you provide the schema, sample data and expected results it would be easier to help you out. You haven't provided schema for the Administrator table or the full schema for the "Action" table.

An infinite universe is the ultimate cartesian product.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-23 : 08:24:12

Something like this:


select
a.Action1ID,
a1.name as A1Name,
a.Action2ID,
a2.name as A2Name,
...
from Action a
left join Administrator a1 on a.Action1Id=a1.ID
left join Administrator a2 on a.Action2Id=a2.ID
...
where ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:36:54

quote:
Originally posted by mattt

Hi,

Imagine I have a table like this ...

Action1ID int
Action1Date datetime
Action2ID int
Action2Date datetime
Action3ID int
Action3Date datetime

And so on ... in fact there's more than ten actions to record in thsi table.

The ID fields all join to an Administrator table which includes a name field, but it's possible for each action to be completed by a different member of staff, so ...

Action1ID = 1
Action2ID = 32
Action3ID = 5

Is there a way I can write a query with a single join to the Administrator table that will pull out all the names in one go? i.e.

Action1ID = 1
name = 'Bob Smith'
Action2ID = 32
name = 'Robert Smith'

etc

something like


SELECT *
FROM
(
SELECT *
FROM
(SELECT * FROM YourTable) t
UNPIVOT (ActionID FOR Values IN ([Action1ID],[Action2ID],...))u
)m
INNER JOIN Administrator a
ON a.ID=m. ActionID

remember to replace * by actual values

mattt
Posting Yak Master

194 Posts

Posted - 2009-01-26 : 05:21:52

quote:

Originally posted by webfred

Something like this:


select
a.Action1ID,
a1.name as A1Name,
a.Action2ID,
a2.name as A2Name,
...
from Action a
left join Administrator a1 on a.Action1Id=a1.ID
left join Administrator a2 on a.Action2Id=a2.ID
...
where ...

I had a horrible feeling that was going to be the answer :) Thanks.

The pivot solution looks neat, but we're still on SQL 2000 here, so I don't have the funcationality.

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:35:57

ok...no problem

i gave it as you posted in sql 2005 forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources