mysql select query for multiple ID's that call name from same username table

Quick question. feel like a noob but haven't found the right syntax for this yet.

Have 3 tables.

T1- AsessessmentID(auto Incr) inspectorID, assistantID, plant name

T2- UserID(auto Incr), username, name

T3- plantID(auto Incr) PlantName

Basically I want to have a php/mysql table that is very similar to T1 but instead of listing the ID's I would like the names. Heres my call so far:

SELECT DISTINCT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName 
                FROM assessmentscores AS a, user AS u, plant AS p
                WHERE u.userID=a.InspectorID AND u.Name='$name' AND p.PlantID=a.Pla开发者_开发问答nt

This works as u.Name will give me the name for the inspector but I can't figure out how to call the second (a.PlantAssistID). Just keep getting the same name for both.

What call to the server should I use to return two different names. Any help would be appreciated

First things first: if you want to select from the assessment table and augment the resultset using the other tables, think about using LEFT JOIN. If you want a more restrictive result, where only assessments are returned that are actually associated with existing users, use an INNER JOIN instead. The example below uses LEFT JOIN.

Not sure whether this is what you want exactly, but you can try:

SELECT a.AssessmentID as a_id, u.Name as u_name, a.PlantAssistID as a_plantassistid, p.PlantName as p_plantname, uass.Name as u_ass_name
    FROM assessmentscores a
    LEFT JOIN user u ON u.UserID = a.AssessmentID
    LEFT JOIN plant p ON p.PlantID = a.Plant
    LEFT JOIN user uass ON uass.UserID = a.PlantAssistentID

Result:

array(
    'a_id'            => int    /* AssessmentID */
    'u_name'          => string /* User name */
    'a_plantassistid' => int    /* Assessment.PlantAssistID */
    'p_plantname'     => string /* Plant name */
    'u_ass_name'      => string /* The user name of the user with assistent_id */
);

I'm not 100% sure I follow you, but here's what I think you want:

I would rewrite the query to use JOIN, as the can be easier to read

SELECT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName, u2.Name as AssisantName
FROM user AS u
    JOIN assessmentscores AS a ON u.UserID = a.InspectorID
    JOIN plant AS p ON p.plantID = a.Plant
    JOIN user as u2 ON u2.UserID = a.assistantId
WHERE u.Name='$name'

The key here is to rejoin on the user table but to give it a different alias (u2 here)