How To Make A Nested Query?

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

Solution 1:

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

Solution 2:

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
    GROUPBY 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
GROUPBY user1.id;