473,397 Members | 2,116 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,397 software developers and data experts.

Inner join to lookup values twice?

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
Jul 20 '05 #1
4 8416
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

Jul 20 '05 #2
[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
Jul 20 '05 #3
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



Jul 20 '05 #4
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

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
4
by: DCM Fan | last post by:
{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B...
8
by: kieran | last post by:
Hi, I have the following sql statement. I originally had the statement with two INNER JOINS but in some situations was getting an error so changed the last INNER JOIN to a LEFT OUTER JOIN (as...
3
by: mheydman | last post by:
I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult...
5
by: jason.evans | last post by:
Hi there. I am having an intrigueing problem. I have a query which left joins another query to itself twice. The original query is derived from a linked table in SQLServer 2000. When I run...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
0
by: katupilar | last post by:
I am writing a tool to interface with a couple of tables in SQL Server 2000. I usually write my queries with an Inner Join to bring in fields from seperate tables and load that to a DataSet. I'm...
0
by: stanlew | last post by:
Happy New Year everyone! I'm new to both T-SQL and this forum. I'm currently doing an internship and my first task was to create a small program which will send an email detailing the sales of the...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.