user parent table id in nested subquery

SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM 
                (SELECT user_id 
                       FROM (
                                SELECT sender_id AS user_id 
                                FROM `connections`
                                WHERE receiver_id=users.id AND status='2' 
                                UNION 
                                SELECT receiver_id AS user_id 
                                FROM `connections` 
                                WHERE sender_id=users.id AND 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)

     FROM users

Error:


#1054 - Unknown column 'users.id' in 'where clause'

How to use pass value to sub query

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.

StackExchange Database Administrators Q#198455, answer score: 9