Mysql query with Union All and Join for League table

Question

for a soccer league i have two tables:

"teams" is a table that lists all teams as unique entries ('id', 'team_name')
"matches" is a table that shows the match results: In the columns 'home' and 'away' i save each game result. 'home_team_id' and 'away_team_id' is the association to the teams-table.

This is how my query looks like:

SELECT Teams, Sum(P) as 'Matches', Sum(W) as 'win', Sum(D) as 'draw', Sum(L) as 'lost',
       SUM(Pts) as 'points'
  FROM (
        SELECT home Teams, 1 P,
                IF (home > away,1,0) W,
                IF (home = away,1,0) D,
                IF (home < away,1,0) L,
                CASE 
                    WHEN home > away THEN 3
                    WHEN home = away THEN 1 
                    ELSE 0
                END PTS
          FROM `matches`

        UNION ALL
        SELECT away Teams, 1,
                IF (home < away,1,0),
                IF (home = away,1,0),
                IF (home > away,1,0),
                CASE 
                    WHEN home < away THEN 3
                    WHEN home = away THEN 1
                    ELSE 0
                END
          FROM `matches`
        ) AS ERG
GROUP BY Teams
ORDER BY SUM(Pts) DESC

Now i want the team names (teams.team_name) from the team-table. To achieve this i tried several join-statements with no luck.

It's obvious that the teams-table can contain teams who did not attend a match. These teams need to be displayed with zero-results.

Therefore I tried a LEFT JOIN:

SELECT team_name AS 'Teams'
  FROM `teams`
  LEFT JOIN matches ON ( teams.id = matches.home_team_id )

right after the ORDER-line in the end. I got an error message. I use MySQL 5.1.44, so nested selects shouldn't be a problem.

Any idea?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT team_name AS 'Mannschaft' FROM `teams` LEFT JOIN matches ON ( teams.id = ' at line 20 — flyte321, Sep 25 '12 at 10:50
Couldn't see the error when I tried fiddling it at http://sqlfiddle.com/#!2/7d97b/1 (OK, there's no data here but am just checking for errors) — Steve Chambers, Sep 25 '12 at 11:00

Taryn · Accepted Answer · 2012-09-25T15:23:46.080

1

It sounds like you want to do something like this. I updated your sub-queries to include the home_team_id and the away_team_id, then you will JOIN on the teams table to return the name.:

SELECT Teams, 
  Sum(P) as 'Matches',
  Sum(W) as 'win',
  Sum(D) as 'draw',
  Sum(L) as 'lost',
  SUM(Pts) as 'points',
  h.team_name as HomeTeam,
  a.team_name as AwayTeam
FROM
(
  SELECT home Teams,
    1 P,
    IF (home > away,1,0) W,
    IF (home = away,1,0) D,
    IF (home < away,1,0) L,
    CASE WHEN home > away THEN 3 WHEN home = away THEN 1 ELSE 0 END PTS,
    home_team_id,
    away_team_id
  FROM `matches`

  UNION ALL

  SELECT away Teams,
    1,
    IF (home < away,1,0),
    IF (home = away,1,0),
    IF (home > away,1,0),
    CASE WHEN home < away THEN 3 WHEN home = away THEN 1 ELSE 0 END,
    home_team_id,
    away_team_id
  FROM `matches`
) AS ERG
LEFT JOIN `teams` h
  on ERG.home_team_id = h.home_team_id
LEFT JOIN `teams` a
  on ERG.away_team_id = a.away_team_id
GROUP BY Teams, home_team_id, away_team_id
ORDER BY SUM(Pts) DESC

Edit #1 based on your comments, it sounds like you want this (See SQL Fiddle with Demo):

SELECT TeamId, 
  t.team_name,
  Teams, 
  Sum(P) as 'Matches', 
  Sum(W) as 'win', 
  Sum(D) as 'draw', 
  Sum(L) as 'lost',
  SUM(Pts) as 'points'
FROM 
(
  SELECT home_team_id TeamId, 
      home Teams, 1 P,
      IF (home > away,1,0) W,
      IF (home = away,1,0) D,
      IF (home < away,1,0) L,
      CASE 
          WHEN home > away THEN 3
          WHEN home = away THEN 1 
          ELSE 0
      END PTS
  FROM `matches`

  UNION ALL
  SELECT away_team_id TeamId,
       away Teams, 1,
       IF (home < away,1,0),
       IF (home = away,1,0),
       IF (home > away,1,0),
       CASE 
           WHEN home < away THEN 3
           WHEN home = away THEN 1
           ELSE 0
        END
   FROM `matches`
) AS ERG
LEFT JOIN `teams` t
  ON ERG.TeamId = t.id
GROUP BY Teams, TeamId
ORDER BY SUM(Pts) DESC

edited Sep 25 '12 at 15:23

answered Sep 25 '12 at 10:52

Taryn

242,637
56
362
405

Now i get an Unknown column 'h.home_team_id' in 'on clause' – flyte321 Sep 25 '12 at 10:58
Many thanks, but now i get: "Unknown column 'a.team_name' in 'field list" – flyte321 Sep 25 '12 at 11:03
@flyte321 without seeing data and/or the full table structure I am guessing based on your original question. Do you have a field called `team_name` in the `teams` table? – Taryn Sep 25 '12 at 11:05
Yes, i have two columns in teams: "id" and "team_name" – flyte321 Sep 25 '12 at 11:10
the table is quite simple, it looks exactly like this: `id home_team_id home away_team_id away played created_at updated_at matchtime matchdate notes location` – flyte321 Sep 25 '12 at 11:15
@flyte321 can you post some sample data or even create a [SQL fiddle](http://sqlfiddle.com/) with a working data model? – Taryn Sep 25 '12 at 12:06
@flyte321 and what do you want the final product to be, both teams in each match or just the team that won the match? – Taryn Sep 25 '12 at 12:48
I want a score table with one column team and all the lost, won games that belongs to this team. In principle what i have posted originally - but including the team name instead of the ID – flyte321 Sep 25 '12 at 12:55
very cool, thxs! We are close, very close. There is one thing I wanted to achieve as well: All the teams in the team-table should be shown - even if they did not attend any matches so far. So that only a zero-entry is shown where the match points a normally shown. That was initially the reason, why I tried it with a left join – flyte321 Sep 25 '12 at 15:03
thats it! Many thanks! Can you pls. send me a message? see my profile – flyte321 Sep 25 '12 at 15:28
tested it a bit...there's still something wrong, see fiddle, TeamID 44 is shown twice, but it only exists once. – flyte321 Sep 25 '12 at 17:32

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

You can use an outer join like this:

SELECT a.team_name, Sum(ERG.P) as 'Matches', Sum(ERG.W) as 'win', Sum(ERG.D) as 'draw', Sum(ERG.L) as 'lost',
       SUM(Pts) as 'points'
  FROM 
    Teams a
    left outer join
    (
    SELECT home Teams, 1 P,
            IF (home > away,1,0) W,
            IF (home = away,1,0) D,
            IF (home < away,1,0) L,
            CASE 
                WHEN home > away THEN 3
                WHEN home = away THEN 1 
                ELSE 0
            END PTS
      FROM `matches`

    UNION ALL
    SELECT away Teams, 1,
            IF (home < away,1,0),
            IF (home = away,1,0),
            IF (home > away,1,0),
            CASE 
                WHEN home < away THEN 3
                WHEN home = away THEN 1
                ELSE 0
            END
      FROM `matches`
    ) AS ERG
    on a.team_name=ERG.Teams
GROUP BY a.team_name
ORDER BY SUM(Pts) DESC

which should do the trick nicely for you.

I also wrote a rather lengthy question and answer which explains unions, joins, outer joins and all sorts of other goodies - which I link to when answering questions like this. It is very long and detailed and explains all the steps in detail as it goes.

edited May 23 '17 at 11:45

Community

1
1

answered Sep 25 '12 at 10:52

Fluffeh

33,228
16
67
80

thanks, but this results in a "Unknown column 'b.P' in 'field list'" – flyte321 Sep 25 '12 at 11:05
@flyte321 Sorry, missed your alias when I was writing the top half of the query. The edit should hopefully do the trick. – Fluffeh Sep 25 '12 at 11:07
the error is gone, but now there is a new error: Unknown column 'a.teams' in 'on clause' – flyte321 Sep 25 '12 at 11:12
@flyte321 Sorry, I was guessing at the column name, it seems you added the syntax (and column name). Changing it to `a.team_name` in the select, join and group by clauses will hopefully do the trick. – Fluffeh Sep 25 '12 at 11:15
now I do not get an error anymore. But only the first line of the output is correct. It looks like this `team_name Matches win draw lost points 1. Superteam 2 0 0 2 0` all the other lines are filled with zeros, except the team name column is correct – flyte321 Sep 25 '12 at 11:40

Mysql query with Union All and Join for League table

2 Answers2