Advice on how to complete specific MySQL JOIN

Question

Hello,

I have a mysql table jobs.

This is the basic structure of jobs.

id
booked_user_id
assigned_user_id

I then also have another table, meta.

Meta has the structure:

id
user_id
first_name
last_name

Here is my php code

$sQuery = "
        SELECT SQL_CALC_FOUND_ROWS job_id, job_name, priority_id, meta.first_name, date_booked
        FROM jobs
        LEFT JOIN (meta) on (meta.user_id = jobs.booked_user_id)
        LEFT JOIN (jobs_priorities) on (jobs_priorities.id = jobs.priority_id)
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query($sQuery);

    while ( $aRow = mysql_fetch_assoc( $rResult ) )
    {
        $sOutput .= '"'.addslashes($aRow['job_id']).'",';
    }

How can I join these tables so that both booked_user_id and assigned_user_id can access meta.first_name?

When I try

$sOutput .= '"'.addslashes($aRow['first_name']).'", 

nothing happens

Thanks for your advice

Tim

Answer 1

You can join twice:

SELECT j.id, b.first_name, a.first_name
FROM jobs j
JOIN meta b ON j.booked_user_id = b.user_id
JOIN meta a ON j.assigned_user_id = a.user_id

Answer 2

Nathan did the fix, but will apply it to your current SQL so you can understand it more

Lets transform your query into this:

    SELECT SQL_CALC_FOUND_ROWS job_id, job_name, priority_id, date_booked
    FROM jobs j
    LEFT JOIN meta b ON b.user_id = j.booked_user_id
    LEFT JOIN meta a ON a.user_id = j.assigned_user_id
    LEFT JOIN jobs_priorities jp ON jp.id = j.priority_id
    $sWhere
    $sOrder
    $sLimit

What I did is to use alias to method and join twice the meta, (just like what nathan did), I temporarily removed the first_name field,

Then let's add something on the SELECT so you can display both first_name

    SELECT SQL_CALC_FOUND_ROWS job_id, job_name, priority_id, date_booked, b.first_name as booked_first_name, a.first_name as assigned_first_name
    FROM jobs j
    LEFT JOIN meta b ON b.user_id = j.booked_user_id
    LEFT JOIN meta a ON a.user_id = j.assigned_user_id
    LEFT JOIN jobs_priorities jp ON jp.id = j.priority_id
    $sWhere
    $sOrder
    $sLimit

Now, we added the column booked_first_name and assigned_first_name, now you can call it on your php code like this:

$aRow['booked_first_name'] or $aRow['assigned_first_name']