mysql select in another select group: how many people in downline?

Question

Hello i've a table similar to this one:

id     sponsor     name
------------------------
1      0          Sasha
2      1          John
3      1          Walter
4      3          Ashley
5      1          Mark
6      4          Alexa       
7      3          Robert
8      3          Frank
9      4          Marika
10     5          Philip
11     9          Elizabeth

when i choose an ID (call it MYCHOICE) i want know all the name of people who has sponsor like MYCHOICE... is simply:

select * from tablename where sponsor=MYCHOICE

but... here is the problem... i would know how many people there is in the downline of this results... so... how many records there are with sponsor like each id.

if i choose id 1 result should be

id  name     downline
----------------------
2   John     0              (noone with sponsor=2)
3   Walter   3              (3 with sponsor=3: ashley, robert, frank)
5   Mark     1              (1 with sponsor=5: philip)

if i choose id 4 result should be

id  name     downline
----------------------
6   Alexa    0
9   Marika   1   (1 with sponsor=9: Elizabeth)

i try this "bad solution" if mychoice is 1

select sponsor,count(*) as downline from tablename where sponsor in (select id from tablename where sponsor=1) group by sponsor order by downline desc

result of this query is

sponsor  downline
---------------------
3        3
5        1

there are 2 problems: - names are not rights and is not that i want - the count 0 "2|John|0" in the example dont appears

thank u for advice and help, sorry for english, N.

Answer 1

SELECT  child.id,
        child.name,
        COUNT(grandchild.sponsor) downline
FROM    TableName child
        INNER JOIN TableName parent
            ON  child.sponsor = parent.id AND
                parent.id = ?              -- << user choice
        LEFT JOIN TableName grandchild
            ON child.id = grandchild.sponsor
GROUP   BY child.id, child.name

• SQLFiddle Demo

As you can see, the table is joined to itself twice. The first join that uses INNER JOIN gets the records associated with the Sponsor which is your user_choice. The second join which uses LEFT JOIN gets all the records associated with records from your user_choice.