SQL counting data from the second table of INNER JOIN, but shouldn’t?

Question

I have 2 tables:

• sponsors
• quotas

What I need to do is check how many nominees the sponsors have but only count the users who have the record in the table cotas. The SQL I made is as follows:

SELECT COUNT(*) AS quantidade_indicados, p.id_patrocinador FROM patrocinadores AS p INNER JOIN cotas AS c ON c.id_user = p.id_usuario WHERE c.status = 1 GROUP BY p.id_patrocinador

The problem of the above code is that it is returning me the amount of rows returned in the table cotas. The user may sometimes have more than 1 record in the quota table with status 1, but as much as he has 500 records with status 1 (active) he should only count 1.

The result I was hoping for was something like:

quantidade_indicados | id_patrocinador
3                         10

That is, the sponsor of ID 10 has 3 listed assets (the assets are checked in the quota table)

The problem is if any of the nominees has 300 entries in the table with the status = 1, then it counts the quantity_indicated as 300 and not as 1.

Answer 1

Try that one:

SELECT count(id) as indicados, id_patrocinador from patrocinadores WHERE 
 id_usuario IN (SELECT id_user FROM cotas WHERE status = 1) group by 
 id_patrocinador