mysql - Performing a Union and a Join -

i have following tables in mysql:

schedule

+-------------+---------------------+-----------+------------+-----------+------------+-------+-----------------+ | schedule_id | schedule_datetime   | team_home | score_home | team_away | score_away | venue | schedule_status | +-------------+---------------------+-----------+------------+-----------+------------+-------+-----------------+ |           2 | 2015-05-05 20:00:00 |         4 |         75 |         5 |   64       |     1 |               2 | |           3 | 2015-05-05 22:00:00 |         7 |         34 |         6 |   85       |     2 |               2 | |           4 | 2015-05-05 21:00:00 |         2 |         74 |         1 |  101       |     1 |               2 | |           5 | 2015-05-05 17:00:00 |         8 |         96 |         3 |   85       |     2 |               2 | |           6 | 2015-05-06 16:00:00 |         4 |        105 |         3 |  101       |     2 |               2 | |           7 | 2015-05-06 17:30:00 |         1 |         96 |         8 |   74       |     2 |               2 | |           8 | 2015-05-06 17:00:00 |         6 |         82 |         2 |   90       |     1 |               2 | |           9 | 2015-05-06 19:00:00 |         5 |         76 |         7 |  101       |     1 |               2 | |          10 | 2015-05-07 16:00:00 |         4 |         85 |         6 |   92       |     1 |               2 | |          11 | 2015-05-07 18:00:00 |         5 |         74 |         1 |   64       |     2 |               2 | |          12 | 2015-05-07 19:00:00 |         7 |        101 |         3 |  113       |     1 |               2 | |          13 | 2015-05-07 21:00:00 |         2 |         75 |         8 |  105       |     2 |               2 | |          14 | 2015-05-08 16:00:00 |         4 |         64 |         7 |   36       |     1 |               1 | |          15 | 2015-05-08 17:30:00 |         2 |         55 |         5 |   72       |     2 |               1 | |          16 | 2015-05-08 17:00:00 |         8 |         80 |         6 |   91       |     1 |               1 | |          17 | 2015-05-08 21:00:00 |         3 |        115 |         1 |   96       |     2 |               1 | |          18 | 2015-05-09 16:00:00 |         4 |        104 |         8 |   74       |     1 |               2 | |          19 | 2015-05-09 19:00:00 |         3 |        101 |         2 |  105       |     1 |               2 | |          20 | 2015-05-09 15:00:00 |         1 |         94 |         7 |   80       |     2 |               2 | |          21 | 2015-05-09 18:00:00 |         6 |        101 |         5 |  110       |     2 |               2 | |          22 | 2015-05-10 18:00:00 |         4 |          0 |         1 |    0       |     1 |               0 | |          23 | 2015-05-10 19:30:00 |         6 |          0 |         3 |    0       |     1 |               0 | |          24 | 2015-05-10 19:00:00 |         5 |          0 |         8 |    0       |     2 |               0 | |          25 | 2015-05-10 21:00:00 |         7 |          0 |         2 |    0       |     2 |               0 | |          26 | 2015-05-11 18:00:00 |         4 |          0 |         2 |    0       |     1 |               0 | |          27 | 2015-05-11 21:00:00 |         8 |          0 |         7 |    0       |     1 |               0 | |          28 | 2015-05-11 18:00:00 |         3 |          0 |         5 |    0       |     2 |               0 | |          29 | 2015-05-11 21:00:00 |         1 |          0 |         6 |    0       |     2 |               0 | +-------------+---------------------+-----------+------------+-----------+------------+-------+-----------------+ 

teams:

+---------+------------------------+---------------+ | team_id | team_name              | team_division | +---------+------------------------+---------------+ |       1 | new york knicks        |             1 | |       2 | brooklyn nets          |             1 | |       3 | boston celtics         |             1 | |       4 | philadelphia 76ers     |             1 | |       5 | denver nuggets         |             4 | |       6 | minnesota timberwolves |             4 | |       7 | oklahoma city thunder  |             4 | |       8 | portland trail blazers |             4 | |       9 | utah jazz              |             4 | +---------+------------------------+---------------+ 

i'm trying perform following sql (modified following question: mysql volleyball standings) :

select      team_id,     count(*) gp,     sum(is_win) wins,     sum(not is_win) losses (     select     team_home team_id,     score_home > score_away is_win     schedule     schedule_status = 2     union     select     team_away team_id,     score_away > score_home is_win     schedule     schedule_status = 2 ) t1 group team_id order wins desc, losses asc 

instead of reporting id of team, want able retrieve names of team teams table. however, keep getting error message when using following code. know i'm going need join modify code allow me join results together?

select      t1.team_id,     teams.team_name,     count(1) gp,     sum(t1.is_win) wins,     sum(not t1.is_win) losses (     select     team_home team_id,     score_home > score_away is_win     schedule     schedule_status = 2     union     select     team_away team_id,     score_away > score_home is_win     schedule     schedule_status = 2 ) t1 inner join teams on t1.team_id = teams.team_id group t1.team_id order wins desc, losses asc ; 

generally speaking, can treat subqueries other table. generally, because temporary tables have weird limitations, , unions seem not scenarios.