5
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

9

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.