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:
) AS mutualConnection
... = 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
So you are retrieving a column collected from either of two
receiver_id, depending on whether the other matches
users.id. After retrieval, you are checking that the retrieved
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
This is how you could do that without the many nesting levels you have attempted and keeping all the correlations at the same level:
COUNT(DISTINCT CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END)
connections AS c
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)
SELECT sender_id AS user_id
WHERE receiver_id = '4' AND status = '2'
SELECT receiver_id AS user_id
WHERE sender_id = '4' AND status= '2'
) AS mutual_connection_count
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.