Help with JOIN in place of query in while loop

[DrQuincy]

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.

[r937]

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

[DrQuincy]

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