Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: How to show to fields with the same name

    I have a table(reg_user) referencing 3 different users by their ids(unreg_user_id, reg_user_id, uploader_id). I would like to create a query that lists each of their names (which are in the users table) but I don't know how because they each reference the same field in the users table (first_name)

    here is the reg_user table with the users id's
    Code:
    CREATE TABLE `reg_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `unreg_user_id` int(11) NOT NULL,
      `reg_user_id` int(11) NOT NULL,
      `cast_id` int(11) NOT NULL,
      `uploader_id` int(11) NOT NULL,
      `video_id` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      FOREIGN KEY(`unreg_user_id`) REFERENCES `users`(user_id),
      FOREIGN KEY(`reg_user_id`) REFERENCES `users`(user_id),
      FOREIGN KEY(`cast_id`) REFERENCES `cast`(cast_id),
      FOREIGN KEY(`uploader_id`) REFERENCES `videos`(uploader_id),
      FOREIGN KEY(`video_id`) REFERENCES `videos`(vid_id)
    )
    here is the users table with their names
    Code:
    CREATE TABLE `users` (
      `user_id` int(11) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
      `last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
        ...
        ...
    here is what I have so far. I don't know how to display both names

    Code:
    select reg_user.reg_user_id,  users.first_name, reg_user.unreg_user_id,  users.first_name
    from reg_user
    INNER JOIN users
    ON users.user_id = reg_user.reg_user_id
    INNER JOIN 
    ON users.user_id = ... 
    where video_id = 20;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to use table and column aliases
    Code:
    SELECT reg_user.unreg_user_id
         , unreg.first_name AS unreg_first_name
         , unreg.last_name  AS unreg_last_name
         , reg_user.reg_user_id
         , reg.first_name AS reg_first_name
         , reg.last_name  AS reg_last_name
         , reg_user.uploader_id
         , uploader.first_name AS uploader_first_name
         , uploader.last_name  AS uploader_last_name
      FROM reg_user
    INNER 
      JOIN users AS unreg
        ON unreg.user_id = reg_user.unreg_user_id
    INNER 
      JOIN users AS reg
        ON reg.user_id = reg_user.reg_user_id
    INNER 
      JOIN users AS uploader
        ON uploader.user_id = reg_user.uploader_id
     WHERE reg_user.video_id = 20;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have a table(reg_user) referencing 3 different users by their ids(unreg_user_id, reg_user_id, uploader_id).
    I would like to create a query that lists each of their names (which are in the users table)
    But, uploader_id in reg_user table references videos table.
    Code:
    CREATE TABLE `reg_user` (
    ...
    ...
      FOREIGN KEY(`uploader_id`) REFERENCES `videos`(uploader_id),
    ...
    )

  4. #4
    Join Date
    Feb 2012
    Posts
    10
    Thanks again r937. I think you have answered every question I've ever posted.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This might be another option.
    But, I don't know MySQL supports the synax.

    Code:
    SELECT r.unreg_user_id
         , MAX( CASE u.user_id
                WHEN r.unreg_user_id THEN u.first_name
                END ) AS unreg_first_name
         , MAX( CASE u.user_id
                WHEN r.unreg_user_id THEN u.last_name
                END ) AS unreg_last_name
         , r.reg_user_id
         , MAX( CASE u.user_id
                WHEN r.reg_user_id THEN u.first_name
                END ) AS reg_first_name
         , MAX( CASE u.user_id
                WHEN r.reg_user_id THEN u.last_name
                END ) AS reg_last_name
         , r.uploader_id
         , MAX( CASE u.user_id
                WHEN r.uploader_id THEN u.first_name
                END ) AS uploader_first_name
         , MAX( CASE u.user_id
                WHEN r.uploader_id THEN u.last_name
                END ) AS uploader_last_name
     FROM  reg_user AS r
     INNER
      JOIN users    AS u
        ON u.user_id
           IN (r.unreg_user_id , r.reg_user_id , r.uploader_id)
     WHERE r.video_id = 20
     GROUP BY
           r.unreg_user_id
         , r.reg_user_id
         , r.uploader_id
    ;

Posting Permissions

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