Sam Sam - 1 year ago 57
MySQL Question

INNER JOIN with another table gives one field twice?

I am very new to MySQL (sql in general) and I am trying to work something out.
I have 2 tables, the first table contains a list of 'clients', the second table a list of 'users'. In the first table there are columns that contain the userID number of the 'contract manager' and the 'business development'. The ID is just a number, that references the ID number in the 'users' table. The users table then stores the First name, last name, etc of the user in question.

I am trying to run a query that will return the first and last names of both the contract manager, and the business development manager, and then use them in PHP.

I have the following query;

tenantusers AS cm ON cm.tenusr_ID = clients.cli_ContractManager
tenantusers AS bd ON bd.tenusr_ID = clients.cli_BusinessDevelopment
ORDER BY cli_Name;

Which in MySQL dashboard returns the correct results, however I can't use this in PHP because

$t = $row['tenusr_FirstName'] . " " . $row['tenusr_LastName'];

Returns the last result of the query, and not the previous. And truth told, I have no idea how to get them. I've tried to use
and it throws errors.

Any help would be appreciated.

: cli_ID : cli_Name : cli_AddressLine1 : cli_Town : cli_ContractManager : cli_BusinessDevelopment :
: 1 : ACME : On a street : Makeuptington : 1 : 2 :

: tenusr_ID : tenusr_Username : tenusr_LastName : tenusr_Email :
: 1 : Sam : Smithers : :
: 2 : Tom : Watson : :

I've added the tables - top is the clients table, second the users.

Answer Source

You can't have columns with the same name in PHP. You'll want to SELECT only the columns you need, and alias the name columns with AS

    cm.tenusr_Username AS contract_FirstName, 
    cm.tenusr_LastName AS contract_LastName, 
    bd.tenusr_Username AS business_FirstName, 
    bd.tenusr_LastName AS business_LastName 

You can then use

$t = $row['contract_FirstName'] . " " . $row['contract_LastName']; 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download