CODEWITHSUNDEEP
mysqlsqlsubquery
Antonio Magdić
Antonio Magdić

Reputation: 43

Mysql - can't pass variable into subquery

Here is my query:

SELECT user.*, @UserID:=user.uid,
    (SELECT count(c1) FROM (
    SELECT COUNT(*) AS c1 FROM user_group
    WHERE user_group.uid=@UserID
    GROUP BY user_group.uid
) x1) AS x2

FROM user

Problem is that @UserID in not passed to sub-query

Upvotes: 4

Views: 9763

Answers (2)

John Woo
John Woo

Reputation: 263933

how about using JOIN? I think the result is the same as what you want. And I'm guessing that you want to get the total number of group the user has. Right?

SELECT  a.*, 
        COALESCE(b.totalCount, 0) totalCount,
        COALESCE(b.totalType, 0) totalType
FROM    user a
        LEFT JOIN
        (
            SELECT  uid, 
                    COUNT(*) totalCount, 
                    COUNT(DISTINCT type) totalType
            FROM    user_Group 
            GROUP BY uid
        ) b ON a.uid = b.uid

Upvotes: 2

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9188

Why to pass the variable there? Directly use the outer table reference like this.

SELECT user.*, 
(SELECT COUNT(*) AS c1 FROM user_group
WHERE user_group.uid=user.uid
GROUP BY user_group.uid) AS GroupCount
FROM user;

Upvotes: 4

Related Questions