SQL查询用户好友及共同好友数时报错：Unknown column 'users.user_id'

Hey there, let's break down this SQL error and fix it step by step!

Why You're Seeing This Error #

The Unknown column 'users.user_id' in 'where clause' error happens because of a subquery scope issue. You're trying to reference users.user_id inside the innermost subquery (the one that creates table a), but that subquery is nested inside the SELECT list of your main query. SQL's subquery scope works in layers—inner subqueries can only access tables from their own FROM/JOIN clauses or the immediately surrounding query context. The users table is part of the main query's LEFT JOIN, which is too far up the nesting chain for that inner subquery to see.

Also, that LEFT JOIN users is totally redundant! You can get the friend IDs for user 2 directly from the friends table (just grab user_two_id when user_one_id=2, and user_one_id when user_two_id=2)—no need to join the users table just to get user_id.

Fixed SQL Query #

Let's rewrite this with a cleaner structure using CTEs (Common Table Expressions) to avoid scope issues and make the logic easier to follow:

WITH user_friends AS (
    -- Reusable CTE to get all friends for a given user
    SELECT 
        CASE 
            WHEN user_one_id = @user_id THEN user_two_id 
            ELSE user_one_id 
        END AS friend_id
    FROM friends
    WHERE user_one_id = @user_id OR user_two_id = @user_id
),
user2_friends AS (
    -- Get all friends of user 2
    SELECT friend_id FROM user_friends WHERE @user_id = 2
),
user1_friends AS (
    -- Get all friends of user 1
    SELECT friend_id FROM user_friends WHERE @user_id = 1
)
-- Calculate mutual friends between each of user 2's friends and user 1
SELECT 
    uf.friend_id AS user_id,
    COUNT(uf_common.friend_id) AS mutual
FROM user2_friends uf
-- Get all friends of user 2's current friend
LEFT JOIN user_friends uf_common ON uf_common.friend_id IN (
    SELECT friend_id FROM user_friends WHERE @user_id = uf.friend_id
)
-- Keep only friends that are also in user 1's friend list
INNER JOIN user1_friends uf1 ON uf_common.friend_id = uf1.friend_id
GROUP BY uf.friend_id;

If you prefer not to use CTEs, here's an equivalent version with nested subqueries that fixes the scope problem:

SELECT 
    friend_id AS user_id,
    (
        SELECT COUNT(*)
        FROM (
            -- Get all friends of the current friend of user 2
            SELECT CASE 
                       WHEN user_one_id = f.friend_id THEN user_two_id 
                       ELSE user_one_id 
                   END AS mutual_candidate
            FROM friends
            WHERE user_one_id = f.friend_id OR user_two_id = f.friend_id
        ) AS friend_friends
        JOIN (
            -- Get all friends of user 1
            SELECT CASE 
                       WHEN user_one_id = 1 THEN user_two_id 
                       ELSE user_one_id 
                   END AS user1_friend
            FROM friends
            WHERE user_one_id = 1 OR user_two_id = 1
        ) AS user1_friends 
        ON friend_friends.mutual_candidate = user1_friends.user1_friend
    ) AS mutual
FROM (
    -- Get all friends of user 2 (exclude user 2 themselves)
    SELECT CASE 
               WHEN user_one_id = 2 THEN user_two_id 
               ELSE user_one_id 
           END AS friend_id
    FROM friends
    WHERE user_one_id = 2 OR user_two_id = 2
) AS f
WHERE friend_id != 2;

Key Improvements #

1. Removed redundant JOIN: We no longer need to join the users table since we can extract friend IDs directly from friends.
2. Fixed scope issues: By first getting user 2's friends as a base dataset (either via CTE or outer subquery), inner subqueries can now safely reference those IDs without scope conflicts.
3. Cleaner logic: Using CTEs lets us reuse the "get user friends" logic, making the query easier to read and modify later.

内容的提问来源于stack exchange，提问作者khalid seleem