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

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" <stevehall (AT) hotmail (DOT) com> wrote

[posted and mailed, please reply in news]

Steve Hall (stevehall (AT) hotmail (DOT) com) writes:
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, sommar (AT) algonet (DOT) se

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

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:

Erland,

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

Cheers!

Steve



Erland Sommarskog <sommar (AT) algonet (DOT) se> wrote