Two Join Statements

So I have this query:

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friend_friends a
    JOIN friends_friends b 
      ON b.user_id = a.friend_id
WHERE a.user_id = 1703122278

It retrieves the friends of friends of a specific user.

I have another database that co开发者_开发问答ntains data on people who have said they are NOT friends.

Here is the query to get the list of said not friends:

SELECT friend_id
FROM friends_not_friends
WHERE user_id = 1703122278
AND not_friends = 1

Then I compare the list of not friends, from the list of possible friends and remove the not friends from the list.

How can I combine these two queries without using a sub-query but rather using Joins?

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friend_friends a
  JOIN friends_friends b 
    ON b.user_id = a.friend_id
  LEFT JOIN friends_not_friends n
    ON b.friend_id = n.friend_id
      AND n.user_id = a.user_id
      AND n.not_friends = 1
WHERE a.user_id = 1703122278
  AND n.friend_id IS NULL

This shows the

(1st list of friends of friends of 1703122278)
MINUS
(2nd list of not friends of 1703122278)

I hope you don't want the

list of friends of
   (friends of 1703122278
    minus
    (2nd list of not friends of 1703122278)
   )

Just fooling around, here are the queries using NOT IN. I personally find these more clear, however they may be less efficient regarding speed.

-- Friends of friends of a user
SELECT DISTINCT(b.friend_id) AS possible_id 
FROM friend_friends b                      -- the b and a aliases
WHERE b.user_id IN                         -- can be removed 
  ( SELECT a.friend_id
    FROM friends_friends a
    WHERE a.user_id = 1703122278
  )
;

and the asked one:

-- Friends of friends of a user that  
-- are also not not_friends of the user:
SELECT DISTINCT(b.friend_id) AS possible_id  
FROM friend_friends b                      -- the b, a and n aliases
WHERE b.user_id IN                         -- can be removed too
  ( SELECT a.friend_id
    FROM friends_friends a
    WHERE a.user_id = 1703122278
  )
  AND b.friend_id NOT IN 
  ( SELECT n.friend_id
    FROM friends_not_friends n
    WHERE n.user_id = 1703122278
      AND n.not_friends = 1
  )
;

in oracle i would use MINUS operator.

i thik there is a correlary function in mysql - probably a left join or NOT IN clause

Here an solution that filters the "not friends" on both levels (it filters the not-friends of the user and the not-friends of the friends of the user):

  SELECT
     DISTINCT(b.friend_id) AS possible_id
  FROM
     friend_friends a
     JOIN friend_friends b ON b.user_id = a.friend_id
     LEFT JOIN friends_not_friends nfa
         ON a.friend_id = nfa.user_id and nfa.not_friends = 1
     LEFT JOIN friends_not_friends nfb
         ON b.friend_id = nfb.user_id and nfb.not_friends = 1
  WHERE
     a.user_id = 1703122278
     AND nfa.friend_id IS NULL
     AND nfb.friend_id IS NULL

The "trick" is to do an LEFT JOIN and check if there is no data (NULL).

By the way - is there an reason that you want no subselect? Sometimes the LEFT JOIN-way is faster, but sub-select are often an good choice.

Try a left outer join to the friend_not_friends table (matching on friend_id, filtering on user_id and the not_friends flag) and then eliminate rows where there was a matching row from not_friends (that is, eliminate rows where the not_friend id is NOT NULL)

SELECT DISTINCT(f.friend_id) AS possible_id
  FROM friend_friends a
  JOIN friends_friends f
    ON f.user_id = a.friend_id
  LEFT
  JOIN friends_not_friends n
    ON n.user_id = a.user_id
   AND n.friend_id = f.friend_id
   AND n.not_friends = 1
 WHERE a.user_id = 1703122278
   AND n.user_id IS NULL

The trick is filtering out the rows that matched, leaving the rows that did not find match in the friends_not_friends table. It's the last predicate in the query (n.user_id IS NULL) that does that filtering for you.

Absent the requirement not to use a subquery, then something like this would work as well:

 SELECT DISTINCT(f.friend_id) AS possible_id
   FROM friend_friends a
   JOIN friends_friends f 
     ON f.user_id = a.friend_id
  WHERE a.user_id = 1703122278
    AND NOT EXISTS 
        ( SELECT 1 
            FROM friends_not_friends n 
           WHERE n.friend_id = f.friend_id
             AND n.user_id = a.user_id
             AND n.not_friends = 1
        )