Your Location is: Home > Sql

SQL Join with 3 tables?

From: Belgium View: 2758 Jorge Santos 

Question

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

here is the diagram for that. enter image description here

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;~

enter image description here 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?

Best 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;

Another answer

That's one more join:

select j.nome as nome_jogador, e.nome as nome_equipa, count(*) cnt_golo
from golo g
inner join jogador j on j.id_jogador = g.jogador_id_jogador
inner join equipa e  on e.id_equipa  = j.equipa_id_equipa
group by j.id_jogador, e.id_equipa
order by cnt_golo desc;

Notes:

  • always use explicit joins instead of old-school, implicit joins (with commas in the from clause); this has been a best practice for decades

  • use table aliases to shorten the query, and do qualify all columns in the query with the table they belong to, so the query is unambiguous

  • as Schwern points out in the comments, your diagram and query do not entirely match; this uses the column names of your diagram

  • the group by clause uses the primary keys of the tables whose columns are not aggregated; if that causes issues for some reason, you might need to enumerate all columns that come into play: group by j.id_jogador, e.id_equipa, j.nome, e.nome