Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009

    Unanswered: referencing a lookup table twice in the same query


    I'm using mySQL as the backend for a database and creating my SQL using PHP, I'm trying to create a SELECT statement that will pull back data from a table that references a lookup table twice. The following is a simplification of my scenario:

    TABLE "account_details" has cols "created_by" and "Maintained_by" both of these contain references to users in a table called users.

    TABLE "users" has cols "user_id" and "user_name"

    I'm trying to select the account details and the names of the users from the users table. I also need to pull back the account_details row even if the "created_by" or "maintained_by" columns haven't been completed.

    The solution I came up with is to use leftjoins to pull data back, this pulls back the account_details even if the user data is missing which is as intended however, if the two users have the same id then two recordsets are created. Also, if the correct data is selected, how can I refer to the "created_by" and "maintained_by" user names as after the join they would both have the column name from the user table ie "user_name."

    I'm out of my depth here and would be very grateful for any assistance as I'm pulling my hair out trying to find a solution.

    Many thanks in advance for any help.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    use an alias on the duplicated table name
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    some sample data and your expected results, would help us give you a solution for your requirements. Aside from that something like this should work for you:
    select ad.created_by, usr_cb.user_name, ad.maintained_by, usr_mb.user_name
       from account_details ad
    left join users usr_cb
    on ad.created_by = usr_cb.user_id
    left join users usr_mb
    on ad.maintained_by = usr_mb.user_id
    Dave Nance

Posting Permissions

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