SQL counting data from the second INNER JOIN table, but should not it?

Navigation
1

I have 2 tables:

  • sponsors
  • dimensions

What I need to do is check how many sponsorships have been nominated but only count the users who have the record in the cotas table. 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 with the code above is that it is returning me the amount of rows returned in the cotas table. The user can sometimes have more than 1 record in the quotas table with status 1, but even though he has 500 records with the status 1 (active) he should only count 1.

The result I expected was something like: 

quantidade_indicados | id_patrocinador
3                         10

That is, the sponsor of ID 10 has 3 active referrals (assets are checked in the quotas table)

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

    
asked by anonymous 06.03.2017 / 01:07

1 answer

2

Try this 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
    
06.03.2017 / 02:19
doubt with include php Real-time data filtering using Jquery