SQL Join with 3 tables?

I’m trying to build a database for a football website.

here is the diagram for that.

I’m now trying to make the top scorers page, and I’ve already managed to query the top scorers with the correspondent amount of goals in desc order using this code:

SELECT  jogador.nome_jogador, count(nome_jogador)
FROM golo, jogador
where jogador_id_jogador = id_jogador
group by jogador.nome_jogador
order by count desc;~

What I’m trying to do now, is add a 3rd column with the team (equipa in portuguese) of each top scorer, but I’m not understanding how i can do that because everytime i use one more column in the select query it shows an error. Every goal as a id of the player who scored it, and every player as the id of is team, is it possible to do that?

Answer

First, let’s switch this to standard join syntax.

select jogador.nome_jogador, count(id_golo)
from golo
join jogador on jogador_id_jogador = id_jogador
group by jogador.nome_jogador
order by count desc;~

Note that you should group by ID, not name, in case two players have the same name. Because the ID is unique it’s find to group by ID but select the name.

And you’re counting goals, not names.

select jogador.nome, count(id_golo)
from golo
join jogador on jogador_id_jogador = id_jogador
group by jogador.id_jogador
order by count desc;

Now we can add the third join. We also need to group by the team’s ID if we’re going to include it in the select list. As before you can select the team name because the ID is unique.

select jogador.nome, equipa.nome, count(id_golo)
from golo
join jogador on jogador_id_jogador = id_jogador
join equipa on equipa_id_equipa = id_equipa
group by jogador.id_jogador, equipa.id_equipa
order by count desc;

Finally, if you want to show every player even if they don’t have a goal, use a right join jogador.

selectjogador.nome, equipa.nome, count(id_golo)
from golo
right join jogador on jogador_id_jogador = id_jogador
join equipa on equipa_id_equipa = id_equipa
group by jogador.id_jogador, equipa.id_equipa
order by count desc;

And this makes more sense using from jogador. Then we can ensure all players appear even those with no goals and no team (if that’s what you want).

select jogador.nome, equipa.nome, count(id_golo)
from jogador
left join golo on jogador_id_jogador = id_jogador
left join equipa on equipa_id_equipa = id_equipa
group by jogador.id_jogador, equipa.id_equipa
order by count desc;