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.

rudy
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

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.