Inner join to lookup values twice?

[Steve Hall]

Folks,

My secnario involves two tables - ObservationRegi ster, and Person.
ObservationRegi ster contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegi ster record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name and userID in the Person table... doing that
once (for just one of the UserID fields) is easy - a quick inner join
does the job - but I effectively need to join to the Person table
"twice", for different keys....

Help? Please!? :)

Steve

[DHatheway]

You join to the table twice, using different "table aliases" or "correlatio n
names" Here's a fairly simple example that I believe represents what you
want:

create table person
(UserID varchar(16) not null,
UserName varchar(24) not null,
constraint PK_Person primary key clustered (UserID))

create table ObservationRegi ster
(ORID int not null,
RaisingUser varchar(16) not null,
AssignedUser varchar(16),
ObservationDate datetime,
ObservationComm ent varchar(64),
constraint PK_ObservationR egister primary key clustered (ORID))
go

insert person (UserID,UserNam e) values ('User1','Raise r1')
insert person (UserID,UserNam e) values ('User2','Raise r2')
insert person (UserID,UserNam e) values ('User3','Raise r3')
insert person (UserID,UserNam e) values ('User4','Worke r4')
insert person (UserID,UserNam e) values ('User5','Worke r5')
insert person (UserID,UserNam e) values ('User6','Worke r6')

insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (1, 'user1', null,'21-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (2, 'user2', null,'22-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (3, 'user3', null,'23-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (4, 'user1', 'user4','24-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (5, 'user2', 'user5','25-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdate ) values (6, 'user3', 'user6','26-dec-2003')

select oreg.orid,
pr.username as raiser,
pa.username as assigned
from
observationregi ster oreg
inner join person pr /* 'pr' for person raising */
on oreg.raisinguse r = pr.userid
left outer join person pa /* 'pa' for person assigned */
on oreg.assignedus er = pa.userid

orid raiser assigned
----------- ------------------------ ------------------------
1 Raiser1 NULL
2 Raiser2 NULL
3 Raiser3 NULL
4 Raiser1 Worker4
5 Raiser2 Worker5
6 Raiser3 Worker6

"Steve Hall" <st*******@hotm ail.com> wrote in message
news:4c******** *************** ***@posting.goo gle.com...

Folks,

My secnario involves two tables - ObservationRegi ster, and Person.
ObservationRegi ster contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegi ster record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name and userID in the Person table... doing that
once (for just one of the UserID fields) is easy - a quick inner join
does the job - but I effectively need to join to the Person table
"twice", for different keys....

Help? Please!? :)

Steve

[Erland Sommarskog]

[posted and mailed, please reply in news]

Steve Hall (st*******@hotm ail.com) writes:

My secnario involves two tables - ObservationRegi ster, and Person.
ObservationRegi ster contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegi ster record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name and userID in the Person table... doing that
once (for just one of the UserID fields) is easy - a quick inner join
does the job - but I effectively need to join to the Person table
"twice", for different keys....

Something like this:

SELECT FirstUser = p1.name, SecondUser = p2.name
FROM ObservationRegi ster or
JOIN Person p1 ON or.FirstUserID = p1.UserId
JOIN Person p2 ON or.SecondUserId = p2.UserID
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve Kass]

You can avoid joining twice with this trick:

select
oreg.orid,
max(case when person.userid = oreg.raisinguse r then person.username
end) as raiser,
max(case when person.userid = oreg.assignedus er then person.username
end) as assigned
from
observationregi ster oreg
inner join person
on oreg.raisinguse r = person.userid
or oreg.assignedus er = person.userid
group by oreg.orid

-- Steve Kass
-- Drew University
-- Ref: 08B3FAD8-9929-43B7-AAD5-30E4EBC53ED9

DHatheway wrote:

You join to the table twice, using different "table aliases" or "correlatio n
names" Here's a fairly simple example that I believe represents what you
want:

create table person
(UserID varchar(16) not null,
UserName varchar(24) not null,
constraint PK_Person primary key clustered (UserID))

create table ObservationRegi ster
(ORID int not null,
RaisingUser varchar(16) not null,
AssignedUser varchar(16),
ObservationDate datetime,
ObservationComm ent varchar(64),
constraint PK_ObservationR egister primary key clustered (ORID))
go

insert person (UserID,UserNam e) values ('User1','Raise r1')
insert person (UserID,UserNam e) values ('User2','Raise r2')
insert person (UserID,UserNam e) values ('User3','Raise r3')
insert person (UserID,UserNam e) values ('User4','Worke r4')
insert person (UserID,UserNam e) values ('User5','Worke r5')
insert person (UserID,UserNam e) values ('User6','Worke r6')

insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (1, 'user1', null,'21-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (2, 'user2', null,'22-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (3, 'user3', null,'23-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (4, 'user1', 'user4','24-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (5, 'user2', 'user5','25-dec-2003')
insert observationregi ster (orid, raisinguser, assigneduser,
observationdat e) values (6, 'user3', 'user6','26-dec-2003')

select oreg.orid,
pr.username as raiser,
pa.username as assigned
from
observationregi ster oreg
inner join person pr /* 'pr' for person raising */
on oreg.raisinguse r = pr.userid
left outer join person pa /* 'pa' for person assigned */
on oreg.assignedus er = pa.userid

orid raiser assigned
----------- ------------------------ ------------------------
1 Raiser1 NULL
2 Raiser2 NULL
3 Raiser3 NULL
4 Raiser1 Worker4
5 Raiser2 Worker5
6 Raiser3 Worker6

"Steve Hall" <st*******@hotm ail.com> wrote in message
news:4c******* *************** ****@posting.go ogle.com...

Folks,

My secnario involves two tables - ObservationRegi ster, and Person.
ObservationRe gister contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegi ster record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name and userID in the Person table... doing that
once (for just one of the UserID fields) is easy - a quick inner join
does the job - but I effectively need to join to the Person table
"twice", for different keys....

Help? Please!? :)

Steve

[Steve Hall]

Erland,

Spot on! Huge Thanks - I'd been trying to solve it all afternoon!

Cheers!

Steve

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************@1 27.0.0.1>...

[posted and mailed, please reply in news]

Steve Hall (st*******@hotm ail.com) writes:

My secnario involves two tables - ObservationRegi ster, and Person.
ObservationRegi ster contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegi ster record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name and userID in the Person table... doing that
once (for just one of the UserID fields) is easy - a quick inner join
does the job - but I effectively need to join to the Person table
"twice", for different keys....

Something like this:

SELECT FirstUser = p1.name, SecondUser = p2.name
FROM ObservationRegi ster or
JOIN Person p1 ON or.FirstUserID = p1.UserId
JOIN Person p2 ON or.SecondUserId = p2.UserID