删除出现在另一个查询中的1个查询的结果

Question

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 = 'SELECT * 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.

Answer 1

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
)