In the id_team field, display the name from the teams table. It is necessary to connect two fields from one table with one field from another. The fact is that there is a field id_team_one and id_team_two.

  select *, win * 3 as win_ace, draw * 1 as draw_ace from( SELECT team_id, id_tournament, name_team_one, name_team_two, sum(win) AS win, sum(loss) AS loss, sum(draw) AS draw, sum(goals_s) AS goals_s, sum(goals_m) AS goals_m FROM (SELECT games.id_team_one as team_id, id_tournament, t1.name as name_team_one, IF(games.goals_one>games.goals_two,1,0) AS win, IF(games.goals_one<games.goals_two,1,0) AS loss, IF(games.goals_one=games.goals_two,1,0) AS draw, games.goals_one AS goals_s, games.goals_two AS goals_m FROM games WHERE games.`datetime` < NOW() UNION ALL SELECT games.id_team_two, id_tournament, t2.name as name_team_two, IF(games.goals_one<games.goals_two,1,0) AS win, IF(games.goals_one>games.goals_two,1,0) AS loss, IF(games.goals_one=games.goals_two,1,0) AS draw, games.goals_two AS goals_s, games.goals_one AS goals_m FROM games WHERE games.`datetime` < NOW() ) t join `tournaments` on id_tournament=tournaments.id join `teams` t1 on id_team_one=t1.id_team join `teams` t2 on id_team_two=t2.id_team GROUP BY team_id ) as foo order by ((win * 3) + (draw * 1)) desc

Added join to teams table. Gives an error message. # 1054 - Unknown column 't1.name' in 'field list'

  • I would add fields like 'ONE' AS team_name and 'TWO' AS team_name to requests and consider it in the grouping - Chubatiy
  • 1) Where is the structure of the tables? 2) If in one table there are two fields referring to the second table, then in the query data source you need to use 2 copies of the second table. - Akina
  • Updated the question. - Miron
  • Gives an error message. # 1054 - Unknown column 't1.name' in 'field list' Right, you don't have such a table at this point. - Akina

1 answer 1

I've done everything. Thank you all for your help.

 select *, win * 3 as win_ace, draw * 1 as draw_ace from( SELECT team_id, id_tournament, team_name, sum(win) AS win, sum(loss) AS loss, sum(draw) AS draw, sum(goals_s) AS goals_s, sum(goals_m) AS goals_m FROM (SELECT games.id_team_one as team_id, id_tournament, t1.name as team_name, IF(games.goals_one>games.goals_two,1,0) AS win, IF(games.goals_one<games.goals_two,1,0) AS loss, IF(games.goals_one=games.goals_two,1,0) AS draw, games.goals_one AS goals_s, games.goals_two AS goals_m FROM games join `teams` t1 on id_team_one=t1.id_team WHERE games.`datetime` < NOW() UNION ALL SELECT games.id_team_two, id_tournament, t2.name as team_name, IF(games.goals_one<games.goals_two,1,0) AS win, IF(games.goals_one>games.goals_two,1,0) AS loss, IF(games.goals_one=games.goals_two,1,0) AS draw, games.goals_two AS goals_s, games.goals_one AS goals_m FROM games join `teams` t2 on id_team_two=t2.id_team WHERE games.`datetime` < NOW()) t join `tournaments` on id_tournament=tournaments.id GROUP BY team_id ) as foo order by ((win * 3) + (draw * 1)) desc