Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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.userid
    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    Originally posted by r937
    Code:
    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.userid
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jul 2003
    Posts
    24
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •