remove results of 1 query that appear in another

I have a query that retrieves the name of each friend a user has by joining that of friends and users tables. I have another table that stores active users. I need to retrieve friends that are active and not active but for some reason I am drawing a blank. If I have a list of all friends and a list of active friends, can I subtract active from all to be left with offline? All I Want to do basically is have two tabs. Under one will be offline friends. Under the other will be online friends. If anyone has any useful suggestions, I would appreciate it.

 $sql = 'SELE开发者_JAVA技巧CT * FROM users
LEFT JOIN friendships
ON friendships.friend_id = users.id
WHERE friendships.user_id = ?';
$stmt5 = $conn->prepare($sql);
$result=$stmt5->execute(array($userid));

$count=$stmt5->rowCount();

//user has more than 0 friends
if ($count>0){
    while ($row = $stmt5->fetch(PDO::FETCH_ASSOC)) {
    $online=htmlspecialchars( $row['username'], ENT_NOQUOTES, 'UTF-8' );
//check whos online
 $sql = 'SELECT * FROM active_users
WHERE username=?';
$stmt7 = $conn->prepare($sql);
$result=$stmt7->execute(array($online));

$count=$stmt7->rowCount();
while ($row = $stmt7->fetch(PDO::FETCH_ASSOC)) {
$activeuser=$row['username'];
}
}

This code just retrieves active users but hopefully gives an idea of structure.

---

Could you do a "not in" clause? Without knowing the layout of your database, I'm thinking something like this:

SELECT * FROM users
LEFT JOIN friendships
ON friendships.friend_id = users.id
WHERE friendships.user_id = ?
AND users.id NOT IN (
  SELECT user_id FROM active_users
)

---

Using SQL to do this 'not in' is probably the best solution.

You could also do this in code if you really want to if the results are ordered. Just loop through the all users list, grab the first result from the active users list, and whenever there's a match, put that on the active users list and grab the next active user. Put every non-match into the inactive users list and only fetch from the all users list.

Something like this might tell you both lists in one shot:

SELECT users.username, active_users.username AS active FROM users
LEFT JOIN friendships
ON friendships.friend_id = users.id
LEFT JOIN active_users ON users.username = active_users.username
WHERE friendships.user_id = ?

Inactive users would return NULL in the active columns, where active would not.