Need help with a multiple Left Join nightmare

[Hobbyist_PHPer]

MySQL Version: 5.0.91-community

 

Hi everyone... So, I have a table that has 2 different UserIDs that I need to get the names for from the Users table, but also I need to get information from a 3rd table based on 1 of the UserIDs... I thought that I could use aliases, but that didn't work so hot... Here's what I have:

 

    echo '<table>';
    $query = mysql_query("SELECT Notes.*, Users.UserFirstName, UserLastName FROM Notes LEFT JOIN Users ON Notes.UserID = Users.UserID WHERE FileID = '$_SESSION[FileID]'");
    while ($row = mysql_fetch_assoc($query)) {
        $subquery = mysql_query("SELECT CasesAndBillableRates.BillableRate, Users.UserFirstName AS OnBehalfOfUserFirstName, Users.UserLastName AS OnBehalfOfUserLastName FROM CasesAndBillableRates LEFT JOIN Users ON CasesAndBillableRates.UserID = Users.UserID WHERE UserID = '$row[WhosUserIDForBillableRate]'");        while ($subrow = mysql_fetch_assoc($subquery)) {
        echo '
        <tr>
            <td style="width: 150px;">Note Creator</td>
            <td style="width: 150px;">'.$row['UserFirstName'].' '.$row['UserLastName'].'</td>
            <td style="width: 150px;">On Behalf Of</td>
            <td style="width: 150px;">'.$subrow['OnBehalfOfUserFirstName'].' '.$subrow['OnBehalfOfUserLastName'].'</td>
        </tr>
        <tr>
            <td style="width: 150px;">Date/Time Added</td>
            <td style="width: 150px;" colspan="4">'; if ($row['NoteDateTimeAdded'] !== '0000-00-00') {echo date("M d, Y", strtotime($row['NoteDateTimeAdded']));} echo '</td>
            <td style="width: 150px;">Billable Time</td>
            <td style="width: 150px;">'. $row['NoteBillableTime'] .' @ '.$subrow['BillableRate'].'</td>
        </tr>
        <tr>
        </tr>
        <tr>
            <td style="width: 150px;" colspan="4">File Note</td>
        </tr>
        <tr>
            <td colspan="4">'. $row['Note'] .'</td>
        </tr>';
        }
    }
    echo '</table>';

[Zane]

SELECT Notes.*,
        (SELECT Users.UserFirstName AS OnBehalfOfUserFirstName, 
             Users.UserLastName AS OnBehalfOfUserLastName, 
             FilesAndBillableRates.BillableRate 
        FROM Users LEFT JOIN FilesAndBillableRates ON Users.UserID = FilesAndBillableRates.UserID 
        WHERE UserID = Users.UserID)
FROM Notes 
WHERE FileID = '{$_SESSION['FileID']}'"

 

Perhaps a subquery SELECT would work.

[Hobbyist_PHPer]

SELECT Notes.*,
        (SELECT Users.UserFirstName AS OnBehalfOfUserFirstName, 
             Users.UserLastName AS OnBehalfOfUserLastName, 
             FilesAndBillableRates.BillableRate 
        FROM Users LEFT JOIN FilesAndBillableRates ON Users.UserID = FilesAndBillableRates.UserID 
        WHERE UserID = Users.UserID)
FROM Notes 
WHERE FileID = '{$_SESSION['FileID']}'"

 

Perhaps a subquery SELECT would work.

 

I tried that and it didn't work, I had to change the subquery WHERE clause, and I think this is where the problem comes in... The identifying UserID is not the UserID, but the second UserID, named WhosUserIDForBillableRate, in the primary table...

 

So here's how I changed your query:

    $query = "SELECT Notes.*, (SELECT FilesAndBillableRates.BillableRate, Users.UserFirstName AS OnBehalfOfUserFirstName, 
Users.UserLastName AS OnBehalfOfUserLastName FROM FilesAndBillableRates LEFT JOIN Users ON FilesAndBillableRates.UserID = Users.UserID 
WHERE FilesAndBillableRates.UserID = Notes.WhosUserIDForBillableRate) FROM Notes LEFT JOIN Users ON Notes.UserID = Users.UserID 
WHERE Notes.FileID = '{$_SESSION['FileID']}'";

[mikosiko]

I personally like using JOINS instead of a correlated query when possible, and after analyze both Explain plans

 

Normally RDBMS are optimized for JOIN operations, therefore the query optimizer will try to rewrite statement to use JOIN operations behind the curtains, and most likely in the majority of cases you are better off using the JOIN approach. But, as always, it depends on the particular situation and what the Explain plans shows.

 

    $query = mysql_query("SELECT a.*, 
                                 b.UserFirstName, 
                                 b.UserLastName,
                                 c.UserFirstName AS OnBehalfOfUserFirstName, 
                                 c.UserLastName AS OnBehalfOfUserFirstName,
                                 d.BillableRate                                   
                            FROM Notes AS a LEFT JOIN Users AS b ON b.UserID = a.UserID
                                            LEFT JOIN Users AS c ON c.UserID = a.WhosUserIDForBillableRate
                                            LEFT JOIN FilesAndBillableRates AS d ON d.UserId = a.WhosUserIDForBillableRate
                            WHERE a.FileID = '$_SESSION[FileID]'");

[Hobbyist_PHPer]

I personally like using JOINS instead of a correlated query when possible, and after analyze both Explain plans

 

Normally RDBMS are optimized for JOIN operations, therefore the query optimizer will try to rewrite statement to use JOIN operations behind the curtains, and most likely in the majority of cases you are better off using the JOIN approach. But, as always, it depends on the particular situation and what the Explain plans shows.

 

    $query = mysql_query("SELECT a.*, 
                                 b.UserFirstName, 
                                 b.UserLastName,
                                 c.UserFirstName AS OnBehalfOfUserFirstName, 
                                 c.UserLastName AS OnBehalfOfUserFirstName,
                                 d.BillableRate                                   
                            FROM Notes AS a LEFT JOIN Users AS b ON b.UserID = a.UserID
                                            LEFT JOIN Users AS c ON c.UserID = a.WhosUserIDForBillableRate
                                            LEFT JOIN FilesAndBillableRates AS d ON d.UserId = a.WhosUserIDForBillableRate
                            WHERE a.FileID = '$_SESSION[FileID]'");

 

Works magnificently, thank you very much... studying it, trying to figure out how it all works... nice work, thanks again...