How to make a nested query?

Question

Have a table users and there is a field invited_by_id showing user id of the person who invited this user. Need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user. Something like this:

SELECT
    User.*, Count.count
FROM
    users AS User,
    (
        SELECT COUNT(*) AS count FROM users WHERE users.invited_by_id=User.id
    ) AS Count;

This one is not working so I need a working one.

Answer 1

Ok, first of all, count is a reserved word in sql so you can't use it as a table alias (unless you quote it in some way but don't do that). Secondly, the real way to solve this problem is to introduce a GROUP BY clause in your subquery.

Try this:

SELECT user3.*, subquery.theCount FROM
    users AS user3
INNER JOIN ( 
    SELECT
        user1.id, count(user2.id) AS theCount
    FROM
        users AS user1
    LEFT OUTER JOIN
        users AS user2 ON user2.invited_by_id=user1.id
    GROUP BY user1.id
) AS subquery ON subquery.id=user3.id;

Here is a dirty little secret about MySQL: It lets you cheat with the GROUP BY statement and select columns that are not in the GROUP BY list and also not in aggregate functions. Other RMDMSes don't let you do this.

SELECT
    user1.*, count(user2.id) AS theCount
FROM
    users AS user1
LEFT OUTER JOIN
    users AS user2 ON user2.invited_by_id=user1.id
GROUP BY user1.id;

Answer 2

SELECT  u.*,
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   ui.invited_by_id = u.id
        ) AS cnt
FROM    users u