user parent table id in nested subquery

MySQL forbids referencing outer-level columns deeper than one level of nesting. Your query, however, is referencing users.id three levels deep.

What you need, therefore, is to rewrite the correlated subquery in such a way that, even if it uses nested queries, the correlation with the outer level is not nested, something like this:

( SELECT COUNT(*) FROM ( SELECT ... ) AS mutualConnection WHERE ... = users.id ) 

The task is quite a challenge because of the way your subquery appears to be correlated with the main query. If I understand it correctly, the logic goes like this:

For every user, find the number of distinct connections (users) who are also connected to a certain other user (in this case user ID='4').

So you are retrieving a column collected from either of two connections columns, sender_id and receiver_id, depending on whether the other matches users.id. After retrieval, you are checking that the retrieved sender_id or receiver_id be among the connections of User 4. Finally, you are counting all distinct occurrences of the resulting column (which, I will repeat, is a mix of sender_id and receiver_id).

This is how you could do that without the many nesting levels you have attempted and keeping all the correlations at the same level:

SELECT u.*, ( SELECT COUNT(DISTINCT CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END) FROM connections AS c WHERE c.status = '2' AND u.id IN (c.sender_id, c.receiver_id) AND (CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END) IN ( SELECT sender_id AS user_id FROM connections WHERE receiver_id = '4' AND status = '2' UNION SELECT receiver_id AS user_id FROM connections WHERE sender_id = '4' AND status= '2' ) ) AS mutual_connection_count FROM users AS u ; 

The CASE expression is the user_id column of your query's tempUser derived table. It is used in the COUNT function as well as in the WHERE clause (the IN predicate). Usually such repetition of code is eliminated by nesting. But nesting cannot be used here because of the MySQL limitation mentioned at the beginning of this post. So, repetition of code is the price you have to pay to work around it. Luckily, there is not much of it in this specific case.

You should move your WHERE user_id = users.id clause to the first correlated subquery.

create table connections(user_id int, sender_id int, receiver_id int, status int); 

create table users(id int); 

SELECT users.*, (SELECT sender_id FROM connections WHERE receiver_id = users.id ) 1_level FROM users; 

 id | 1_level -: | ------: 

SELECT users.*, (SELECT sender_id FROM (SELECT receiver_id as sender_id FROM connections WHERE sender_id = users.id ) 2_level ) 1_level FROM users; 

 Unknown column 'users.id' in 'where clause' 

SELECT users.*, (SELECT COUNT(user_id) AS mutual_connection FROM (SELECT user_id FROM (SELECT sender_id AS user_id FROM connections WHERE status = '2' UNION SELECT receiver_id AS user_id FROM connections WHERE status = '2' ) tempUser WHERE user_id IN (SELECT sender_id AS user_id FROM connections WHERE receiver_id = '4' AND status = '2' UNION SELECT receiver_id AS user_id FROM connections WHERE sender_id = '4' AND status= '2') GROUP BY user_id) as mutualConnection WHERE user_id = users.id #<<<-------------- Here ) uid FROM users 

 id | uid -: | --: 

dbfiddle here