Hi,

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.

Tony

use an alias on the duplicated table name

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:
Dave Nance