mysql can't pass column from query to nested subquery

Question

What I want is to find each friend associated with user2 (id = 2) then find the count of mutual friends between each user2 friend and user1.

mysql to find user2 Friends and get mutual_count of Friends with user1 ( with every user2 friend)

Table users

user_id |   username
------------------
1       |   user1
2       |   user2
3       |   user3
4       |   user4
5       |   user5
6       |   user6
7       |   user7

Table friends

user_one_id |   user_two_id
------------------------
1           |   4
1           |   5
1           |   6
2           |   3
2           |   4
3           |   1
3           |   4
5           |   2
5           |   3
5           |   4
6           |   2
6           |   3
7           |   2

Expected output: Friends of user2 //( friend name , friend id, mutual count of friends between current friend id and user1 (id = 1))

username | user_id  | mutual_count
------------------------
user3   |  3        | 3     // user3 and user1 mutual ->  (user4,user5,user6)
user4   |  4        | 2     // user4 and user1 mutual ->  (user3 ,user5)
user5   |  5        | 2     // user5 and user1 mutual ->  (user3 ,user4)
user6   |  6        | 1     // user6 and user1 mutual ->  ( user3)
user7   |  7        | 0    // user7 and user1 mutual ->   (0)

mysql statement

SELECT  users.username,users.user_id,

    (SELECT count(a.friendID) FROM
        (   SELECT user_two_id friendID FROM friends  WHERE user_one_id =users.user_id
            UNION 
            SELECT user_one_id friendID FROM friends WHERE user_two_id = users.user_id
        ) AS a 
        JOIN  
        (   SELECT user_two_id friendID FROM friends WHERE user_one_id = 1
            UNION 
            SELECT user_one_id friendID FROM friends WHERE user_two_id = 1
        ) AS b 
        ON  a.friendID = b.friendID
    ) as mutual_count

FROM friends LEFT JOIN  users    
ON friends.user_one_id = users.user_id or  friends.user_two_id = users.user_id  
WHERE (friends.user_one_id = 2 OR friends.user_two_id = 2)   AND users.user_id !=2
order by user_id

Error I get Unknown column 'users.user_id' in 'where clause' problem subquery to get mutual_count canot know users.user_id

any help appreciated

Your question is unclear and confusing. Do you want number of mutual friends between user1 and all other users? — Sandesh Gupta
Yes, and the problem seems to be with users.user_id .. in the sub-sub query — Ibrahim Mohamed
as facebook if you logined as user1 and goto user2 friends , you will show mutual numbers between user1 and user2 friends — sami eisa

Ibrahim Mohamed Ibrahim Mohamed · Accepted Answer · 2018-05-12T12:07:52

I modified your attempt to get the desired output (but may be there is a better way to get what you want).

It seems that the main problem is that the users table in the top outer query isn't accessible to all subqueries. So I modified the subquery that needs this table, so that it can access the users table through the WHERE clause in its direct outer query.

SELECT  users.username,users.user_id,

    (SELECT count(a.friendID) FROM
        (   SELECT user_two_id friendID, user_one_id where_id FROM friends
            UNION 
            SELECT user_one_id friendID, user_two_id where_id FROM friends
        ) AS a 
        JOIN  
        (   SELECT user_two_id friendID FROM friends WHERE user_one_id = 1
            UNION 
            SELECT user_one_id friendID FROM friends WHERE user_two_id = 1
        ) AS b 
        ON  a.friendID = b.friendID
        WHERE a.where_id = users.user_id
    ) as mutual_count

FROM friends LEFT JOIN  users    
ON friends.user_one_id = users.user_id or  friends.user_two_id = users.user_id  
WHERE (friends.user_one_id = 2 OR friends.user_two_id = 2)   AND users.user_id !=2
order by user_id

OUTPUT:

+----------+---------+--------------+
| username | user_id | mutual_count |
+----------+---------+--------------+
|   user3  |    3    |       3      |
+----------+---------+--------------+
|   user4  |    4    |       2      |
+----------+---------+--------------+
|   user5  |    5    |       2      |
+----------+---------+--------------+
|   user6  |    6    |       1      |
+----------+---------+--------------+
|   user7  |    7    |       0      |
+----------+---------+--------------+

Modified Lines:

Line 4: SELECT user_two_id friendID, user_one_id where_id FROM friends

Line 6: SELECT user_one_id friendID, user_two_id where_id FROM friends

Line 14: WHERE a.where_id = users.user_id

mysql can't pass column from query to nested subquery

1 Answers