Help w/query - mult. columns same row

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 2000 Forums

Transact-SQL (2000)

Help w/query - mult. columns same row

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-10-29 : 19:51:15

Hello...

I have the following two tables and columns.
Users
UserID
Name

Jobs
JobID
Desc
User1_ID
User2_ID
User3_ID

I'd like to be able to write a query with the results being one row for each JobID...but it would show the ID and Name for person that is associated with a job.

If possible, what I'd like to see output would be something like:

JobID Desc User1_ID Name User2_ID Name User3_ID Name
----- ---- -------- ----- -------- ---- -------- -----
100 Mgr. 23 Joe 56 Jane 88 Kim

Is this possible? When I use UNIONs I get three rows of data, not one.

thanks for any help.
- dw

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-10-29 : 20:14:16

I wrote too soon....I figured it out!

I used the following:

select J.*, U.Name, U1.Name, U2.Name
from Jobs J Inner join Users U ON J.User1_ID = U.UserID inner join Users U1 ON J.User2_ID = U1.UserID
inner join Users U2 on J.User3_ID = U2.Userid

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-10-29 : 20:16:57

I think this works....
Would be easier if you posted CREATE TABLE and INSERT INTO statments for me to test with.


SELECT j.JobID, j.Desc, j.User1_ID, u1.Name, j.User2_ID, u2.Name, 
j.User3_ID, u3.Name
FROM Jobs j
INNER JOIN Users u1 ON u1.UserID = j.User1_ID
INNER JOIN Users u2 ON u2.UserID = j.User2_ID
INNER JOIN Users u3 ON u3.UserID = j.User3_ID

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources