User/Subscriber sql query

Question

I'm creating a table that shows all the registered users to which the current user has not yet subscribed to. But once he has subscribed someone, I need to filter that list to exclude those.

Let's say the theres a table called subscribed which lists the User and to whom he is subscribed to.

|UserId||SubscriberID|

Its easy to make it into multiple queries, but I've been unsuccessfully trying to make it into one query, to save an extra loop of MySQL calls.

Here's What I have so far:

 SELECT u.UserID, FullName, UserName from users u 
    LEFT JOIN subscribed t ON 
     ((u.UserName LIKE '%$search%' OR 
       u.Email LIKE '%$search%') AND 
      ({$_SESSION['ID']} = t.UserID 
        AND t.FollowerID != u.UserID)
     )

I know the last part of the query is wrong, since I only compare if the UserID and the FollowerID don't match for one particular row, not the entire table.

Answer 1

To find a list of results in table A that are not in table B, you have two options. Either use the NOT IN syntax, or LEFT JOIN and look where the PK field in the B table is NULL.

NOT IN example:

SELECT a.id FROM a WHERE a.id NOT IN (SELECT b.id FROM b)

LEFT JOIN example:

SELECT a.id FROM a LEFT JOIN b ON (a.id = b.id) WHERE (b.id IS NULL)