I have 3 tables:
sponsors
id | id_patrocinador | id_usuario
quotas
id | id_user | quantidade
users
id | nome | login
What I need to do is a TOP 10 where I list 10 users who have sponsored with active quotas. For example:
1 - João - 10 indicados ativos
2 - Maria - 9 indicados aitvos
...
I tried SELECT in a number of ways but could not get any right results.
RESOLVED
I've set up an SQL:
SELECT u.nome, u.login, (SELECT COUNT(DISTINCT p.id) FROM patrocinadores AS p INNER JOIN cotas AS c ON c.id_user = p.id_usuario WHERE c.status = 1 AND p.id_patrocinador = u.id) AS quantidade FROM usuarios AS u ORDER BY quantidade DESC LIMIT 10
I'm out of SQL on my machine now Try this:
select Users.id , users.name , count (sponsors.id) from users inner join sponsors on usuarios.id = sponsors.id_user group by users.id, users.name order by 3 desc