Inner join to lookup values twice?

[Steve Hall]

Folks,

My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister 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 ObservationRegister 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 "correlation
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 ObservationRegister
(ORID int not null,
RaisingUser varchar(16) not null,
AssignedUser varchar(16),
ObservationDate datetime,
ObservationComment varchar(64),
constraint PK_ObservationRegister primary key clustered (ORID))
go

insert person (UserID,UserName) values ('User1','Raiser1')
insert person (UserID,UserName) values ('User2','Raiser2')
insert person (UserID,UserName) values ('User3','Raiser3')
insert person (UserID,UserName) values ('User4','Worker4')
insert person (UserID,UserName) values ('User5','Worker5')
insert person (UserID,UserName) values ('User6','Worker6')

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

select oreg.orid,
pr.username as raiser,
pa.username as assigned
from
observationregister oreg
inner join person pr /* 'pr' for person raising */
on oreg.raisinguser = pr.userid
left outer join person pa /* 'pa' for person assigned */
on oreg.assigneduser = 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*******@hotmail.com> wrote in message
news:4c**************************@posting.google.c om...

Folks,

My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister 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 ObservationRegister 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*******@hotmail.com) writes:

My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister 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 ObservationRegister 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 ObservationRegister 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.raisinguser then person.username
end) as raiser,
max(case when person.userid = oreg.assigneduser then person.username
end) as assigned
from
observationregister oreg
inner join person
on oreg.raisinguser = person.userid
or oreg.assigneduser = 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 "correlation
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 ObservationRegister
(ORID int not null,
RaisingUser varchar(16) not null,
AssignedUser varchar(16),
ObservationDate datetime,
ObservationComment varchar(64),
constraint PK_ObservationRegister primary key clustered (ORID))
go

insert person (UserID,UserName) values ('User1','Raiser1')
insert person (UserID,UserName) values ('User2','Raiser2')
insert person (UserID,UserName) values ('User3','Raiser3')
insert person (UserID,UserName) values ('User4','Worker4')
insert person (UserID,UserName) values ('User5','Worker5')
insert person (UserID,UserName) values ('User6','Worker6')

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

select oreg.orid,
pr.username as raiser,
pa.username as assigned
from
observationregister oreg
inner join person pr /* 'pr' for person raising */
on oreg.raisinguser = pr.userid
left outer join person pa /* 'pa' for person assigned */
on oreg.assigneduser = 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*******@hotmail.com> wrote in message
news:4c**************************@posting.google. com...

Folks,

My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister 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 ObservationRegister 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********************@127.0.0.1>...

[posted and mailed, please reply in news]

Steve Hall (st*******@hotmail.com) writes:

My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister 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 ObservationRegister 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 ObservationRegister or
JOIN Person p1 ON or.FirstUserID = p1.UserId
JOIN Person p2 ON or.SecondUserId = p2.UserID