MySQL Left Join Example

Below we give an example of LEFT JOIN syntax for MySQL:

$sql = "SELECT Reviews.ReviewId, Reviews.UserId, Reviews.ReviewRating, Reviews.ReviewDesc, Reviews.ReviewDate, Users.Username FROM Reviews LEFT JOIN Users ON Reviews.UserId = Users.UserId WHERE ProductId LIKE $ProductId AND ReviewIsApproved=1 ORDER BY $sortby $sortdirection LIMIT $offset, $rowsperpage";

There are two tables involved in the query, the Reviews table and the Users table. You'll notice that in the query we qualify each field reference with the table the field is from, e.g. Reviews.ReviewId means the ReviewId field which is found in the Reviews table. The purpose of the left join is to retrieve the Username, which is only found in the Users table, by associating the tables via UserId. The Reviews table contains the following fields, including a UserId field:

ReviewId
ProductId
UserId
ReviewRating
ReviewDesc
ReviewDate
ReviewIsApproved

and the Users table contains a UserId field as well:

UserId
Username

The join allows us to include a Username in our output from the database by referencing $list['Username'] in the results of our query (we haven't given full database retrieval code here, but below is the echoing of the query results):

echo "<tr bgcolor=" . $bg . "><td valign='top'>" . $list['ReviewRating'] . "</td><td>" . nl2br($list['ReviewDesc']) . "<br><br><strong>Review Id:</strong> " . $list['ReviewId'] . " <strong>Reviewer:</strong> " . $list['Username'] ."</td><td valign='top'>" . date('H:i:s, M d Y', strtotime($list['ReviewDate'])) . "</tr>";