How can I not take in consideration values join without record on the db-CodePudding

I'm in front of a "minor" problem taht looks easy but I didn't suceed to resolve it.

I have three tables in my Database :

Table gp

____________
id | name | 
____________
1 | Le Mans|     
2 | Toulon |    
3 | Rennes |

Table player

____________
id | name | 
____________
1 | Thibaut|     
2 | Fred |    
3 | Samir |

Table Records

_____________________________
id | gp_id | player_id | time  
_____________________________
1 | 1 | 1 | 17860    
2 | 2 | 1 | 11311
3 | 3 | 1 | 33133
4 | 3 | 2 | 11113
5 | 2 | 2 | 44444  
6 | 1 | 2 | 13131
7 | 1 | 3 | 11111
8 | 3 | 3 | 21112

I want to get a sum of time for players that have a record on every gp ( so in my case, just players Thibaut and Fred have a record on the 3 gp ( Samir has just a record on two gp ) ).

I have no idea how I can get that, of course this SQL query is retrieving a sum but from this query I want to escape the guys that don't have a record on every GPs, but I'm blocked at that point ...

SELECT p.name, sum(time) 
from records r 
join gp g on r.gp_id = g.id 
join player p on r.player_id = p.id
group by r.player_id

Thanks in advance guys !

CodePudding user response：

You could use having count to exclude the records that don't have a record on every GPs.

Try:

select p.name,
       sum(`time`) as tot_sum
from records r
inner join player p on r.player_id=p.id
inner join gp g on g.id=r.gp_id
group by p.name
having count(distinct gp_id) = (select count(distinct id) from gp)

https://dbfiddle.uk/t8QwSFDY

having count(distinct gp_id) = (select count(distinct id) from gp) will match only the records in the record table that have a record on every gp.