Match Results by team_id in MySQL-CodePudding

I have table like teams, matches and leagues the following screen shots and I need find team results for related team_id. (Home and Away results)

teams table fields: team_id, team_name

leagues table fields: league_id, league_name

matches table fields: match_id, match_date, home_team_id, a.team_id, home_score, away_score, league_id

I can find all results as below query, but how can I find results by team_id.

My needs is finding correct results by using example : where = team_id="21" and shows ** Arsenal's Home & AWAY all matches.**

All Results (Home and Away) by league_id query is;

SELECT DISTINCT m.week,
            m.match_date,
            h.team_name AS Home,
            a.team_name AS AWAY,
            m.home_score,
            m.away_score,
            m.league_id
FROM matches m,
     teams h,
     teams a,
     leagues l
WHERE h.team_id=m.home_team_id
  AND a.team_id=m.away_team_id
  AND m.league_id=2;

Tables

Thanks in advance to those who will help.

Regards.

CodePudding user response：

For A particular league

SELECT DISTINCT m.week,
            m.match_date,
            h.team_name AS Home,
            a.team_name AS AWAY,
            m.home_score,
            m.away_score,
            m.league_id
FROM matches m,
     teams h,
     teams a,
     leagues l
WHERE h.team_id=m.home_team_id
  AND a.team_id=m.away_team_id
  AND l.id=m.league_id
  AND m.league_id=2
  AND (m.home_team_id = 21 or m.away_team_id = 21) ;

for any league

SELECT DISTINCT m.week,
            m.match_date,
            h.team_name AS Home,
            a.team_name AS AWAY,
            m.home_score,
            m.away_score,
            m.league_id
FROM matches m,
     teams h,
     teams a,
     leagues l
WHERE h.team_id=m.home_team_id
  AND a.team_id=m.away_team_id
  AND l.id=m.league_id
  AND (m.home_team_id = 21 or m.away_team_id = 21) ;

CodePudding user response：

Much simpler to understand if you use explicit joins for example

drop table if exists teams,leagues,matches;

create table teams (team_id int, team_name varchar(3));

create table leagues(league_id int, league_name varchar(3));

create table matches 
(match_id int, match_date date, home_team_id int, a_team_id int, home_score int, away_score int, league_id int);

insert into teams values (1,'t1'),(2,'t2');
insert into leagues values(1,'l1');
insert into matches values 
(1,'2022-01-01','1','2',10,50,1),
(2,'2022-01-01','2','1',10,50,1);

select m.*,t1.team_name hometeam,t2.team_name awayteam,l.league_name
from   matches m
join   teams  t1 on m.home_team_id = t1.team_id 
join   teams  t2 on m.a_team_id = t2.team_id
join   leagues l on l.league_id = m.league_id
where  m.home_team_id = '1' or m.a_team_id = 1;

---------- ------------ -------------- ----------- ------------ -----------

- ----------- ---------- ---------- ------------- 
| match_id | match_date | home_team_id | a_team_id | home_score | away_score | league_id | hometeam | awayteam | league_name |
 ---------- ------------ -------------- ----------- ------------ ------------ ----------- ---------- ---------- ------------- 
|        2 | 2022-01-01 |            2 |         1 |         10 |         50 |         1 | t2       | t1       | l1          |
|        1 | 2022-01-01 |            1 |         2 |         10 |         50 |         1 | t1       | t2       | l1          |
 ---------- ------------ -------------- ----------- ------------ ------------ ----------- ---------- ---------- ------------- 
2 rows in set (0.001 sec)