Help with JOIN in place of query in while loop

Here’s a simplified version of the tables I’m working with.

agents: id, name
reviews: id, review, agent, user
users: id, name

With a single query I want to be able to select a page of agents (e.g. LIMIT 0,10) but also show if the current user has left a review of the agent.

I could do it thus (excuse the rough code):

// Assume logged in as user '2'

    $result = mysqli_query($link, "SELECT * FROM agents ORDER BY name ASC LIMIT 0, 10");
    while($row = mysqli_fetach_assoc($result) {
        $result2 = mysqli_query($link, "SELECT * FROM reviews WHERE agent = " . $row['agent'] . " AND user = 2");

        // If result set has a result then a review has been left

So I’d like a row returned with the page of agents on and then extra columns for the user ID and name and the review. Obviously the extra columns would be empty if no review is left. I’m struggling to do it in a single query though. Can it be done? I’m still a bit of a novice with joins.

SELECT something , anything , just_not_the_dreaded_evil_select_star FROM agents LEFT OUTER JOIN reviews ON reviews.agent = AND reviews.user = 2 ORDER BY LIMIT 10

Thanks for this. Sorry, I’m not ignoring you, I’ve been so busy. Will give it a try and let you know. Thanks!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.