How to merge with two queries in MySql?-CodePudding

Even though my previous post was partially correct, I created this post where I didn't get the exact result I wanted.

I cannot get properly answer mI want to find the wins, losses and draws of the team whose team_id is given according to the home and away results. The query I created always shows home team wins, losses and draws regardless of team_id. How can I correctly query the home and away wins, losses and draws of the team specified with the entered team_id?

My tables structures are;

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

And also I cannot merge both queries. I think if both queries possible to merge it will give the result I expected.

Wrong working queries are :

-- Arsenal's Home W,L and D results query
    select t1.team_name hometeam, m.home_score,  t2.team_name awayteam, m.away_score,
             CASE WHEN m.home_score > m.away_score THEN 'W' 
                  WHEN m.home_score < m.away_score THEN 'L' 
                    WHEN m.home_score = m.away_score THEN 'D' 
             END AS results
    from   matches m
    join   teams  t1 on m.home_team_id = t1.team_id 
    join   teams  t2 on m.away_team_id = t2.team_id
    where  m.home_team_id = '21' or m.away_team_id = 21;

Results: (Away Wins shown as Lost and Away Lost shown as Win)

HomeT	HS	AwayT	AS	results
Brentford	2	Arsenal	0	W
Arsenal	0	Chelsea	2	L
Manchester City	5	Arsenal	0	W
Arsenal	1	Norwich City	0	W
Burnley	0	Arsenal	1	L
Arsenal	3	Tottenham Hotspur	1	W
Brighton & Hove Albion	0	Arsenal	0	D
Arsenal	2	Crystal Palace	2	D

And

-- Arsenal's Away W,L and D results
select t1.team_name hometeam, m.home_score,  t2.team_name awayteam, m.away_score,
         CASE WHEN m.away_score > m.home_score THEN 'W' 
              WHEN m.away_score < m.home_score THEN 'L' 
                WHEN m.away_score = m.home_score THEN 'D' 
         END AS results
from   matches m
join   teams  t1 on m.home_team_id = t1.team_id 
join   teams  t2 on m.away_team_id = t2.team_id
where  m.home_team_id = '21' or m.away_team_id = 21;

Results: (Home Wins shown as Lost and Home Lost shown as Win )

HomeT	HS	AwayT	AS	results
Brentford	2	Arsenal	0	L
Arsenal	0	Chelsea	2	W
Manchester City	5	Arsenal	0	L
Arsenal	1	Norwich City	0	L
Burnley	0	Arsenal	1	W
Arsenal	3	Tottenham Hotspur	1	L
Brighton & Hove Albion	0	Arsenal	0	D
Arsenal	2	Crystal Palace	2	D

Thanks in advance to those who will help.

Regards.

CodePudding user response：

Is this what you are looking for?

select t1.team_name hometeam, m.home_score,  t2.team_name awayteam, m.away_score,
        CASE WHEN (m.home_team_id = 21 and m.home_score > m.away_score) or (m.away_team_id = 21 and m.home_score < m.away_score) THEN 'W' 
             WHEN (m.home_team_id = 21 and m.home_score < m.away_score) or (m.away_team_id = 21 and m.home_score > m.away_score) THEN 'L' 
             WHEN m.home_score = m.away_score THEN 'D' 
        END AS results
from matches m
join teams  t1 on m.home_team_id = t1.team_id 
join teams  t2 on m.away_team_id = t2.team_id
where m.home_team_id = 21 or m.away_team_id = 21;