How to make this join select statement works

Here is a select statement of table A:

select user01_id, user02_id from A where ...

I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?

select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)

table A

user01_id int,
user02_id int,
...
primary (user01_id, user02_id)

table user

userid int primary key,
name varchar(80),
...

Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/

Hi, Rudy,

The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.

Thanks very much for your help.

Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy

In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.

A good usage of "defensive". That is the difference between a master and a regular craftsman.