how do I query this?

[Hyrum Mortensen]

Hi,

I have a table that stores a list of IDENTITIES that references
different tables. Two rows in this table refreence the same key from
a table:

tbl_myTable(myTableID, swUserID, hwUserID, etc...)
tbl_user (userID, userName)

where swUserID and hwUserID references userID of the tbl_user table.

How do I do make a query to return the userName of both swUserID and
hwUserID?

[Anith Sen]

To get them as a single row:
--
Anith

[Anith Sen]

To get them as a single row:

SELECT u1.*, u2.*
FROM mytbl t1
INNER JOIN usertbl u1
ON u1.userID = t1.swUserID
INNER JOIN usertbl u2
ON u2.userID = t1.hwUserID ;

To get them as two rows:

SELECT u1.*
FROM mytbl t1
INNER JOIN usertbl u1
ON u1.userID IN (t1.swUserID, t1.hwUserID) ;

--
Anith

[Simon Hayes]

hy****@hotmail.com (Hyrum Mortensen) wrote in message news:<28*************************@posting.google.c om>...

Hi,

I have a table that stores a list of IDENTITIES that references
different tables. Two rows in this table refreence the same key from
a table:

tbl_myTable(myTableID, swUserID, hwUserID, etc...)
tbl_user (userID, userName)

where swUserID and hwUserID references userID of the tbl_user table.

How do I do make a query to return the userName of both swUserID and
hwUserID?

This may be what you're looking for:

select
t.myTableID,
u1.userName as 'swName',
u2.userName as 'hwName'
from
dbo.tbl_myTable t
join dbo.tbl_user u1
on t.swUserID = u1.userID

join dbo.tbl_user u2
on t.hwUserID = u2.userID

If this doesn't help, please post CREATE TABLE statements for both
tables, as well as sample data and the output you expect to see.

Simon