Querying multiple tables

[rico harley]

I need help with this query. The databases looks like this:

Friends


Users


<?php

//suggested friends

$suggestedHTML = '';
    $sql = "SELECT username, avatar FROM users LEFT JOIN friends ON users.id!=friends.user2_id";
    $query = mysqli_query($db_conx, $sql);
    while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
        $suggested_username = $row["username"];
        $suggested_avatar = $row["avatar"];
        if($suggested_avatar != ""){
            $suggested_pic = 'users/'.$suggested_username.'/'.$suggested_avatar.'';
        } else {
            $suggested_pic = 'images/avatardefault.jpg';
        }
        $suggestedHTML .= '<div class="friend-div"><a href="user.php?u='.$suggested_username.'"><img class="friendpics" src="'.$suggested_pic.'" alt="'.$suggested_username.'" title="'.$suggested_username.'"><span id="friend-user">'.$suggested_username.'<span></a></div>';
   
}

 
?>

I need the username and avatar to output on the screen where friend.accepted is != 1, but the query just runs through every record in the friends table.
What is the right approach?
I only want to output the username and avatar for the records in users where there is no friend relationship.

[Jeff Cohan]

I'm sorry I don't have time to replicate your tables and test it, but my top-of-head suggestion is to try this:

select username, avatar from users where id not in (select distinct user2_id from friends)

On Thursday, December 18, 2014 10:31:30 AM UTC-5, rico harley wrote:

Enter code here...

[Jonathon Hill]

As a best practice, avoid using subqueries where possible because they tend to not optimize as well (although MySQL 5.1 and 5.5 made big strides in this area). At any rate, I don't think a subquery is necessary here.

--
You received this message because you are subscribed to the Google Groups "AtlantaPHP Discussions and Job Postings" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atlantaphp+...@googlegroups.com.
To post to this group, send email to atlan...@googlegroups.com.
Visit this group at http://groups.google.com/group/atlantaphp.

To view this discussion on the web visit https://groups.google.com/d/msgid/atlantaphp/6bf0ef7a-abfa-4dd9-8f20-d370d24cc238%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Mike Schinkel]

If I understand your question, I believe this is what you want:

SELECT

username,

avatar

FROM

users

LEFT JOIN friends ON users.id= friends.user2_id

WHERE

friends.id IS NULL

-Mike

--
You received this message because you are subscribed to the Google Groups "AtlantaPHP Discussions and Job Postings" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atlantaphp+...@googlegroups.com.
To post to this group, send email to atlan...@googlegroups.com.
Visit this group at http://groups.google.com/group/atlantaphp.

To view this discussion on the web visit https://groups.google.com/d/msgid/atlantaphp/23b544d6-5c6d-440b-becc-ec275e08f470%40googlegroups.com.