I cannot fine same question in here, so I write it.

I need one SQL query for below details; I have two tables and 2st table has team_id and 2nd table have home_team_id and away_team_id and home and away scores.

I can make home and away matches separately as below queries already:

Home Matches Query :

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 h.team_name ='Real Madrid'  and m.league_id = 1;

Away Matches Query :

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 a.team_name ='Real Madrid'  and m.league_id = 1;

I want to see one team home and away results in same query.

My Tables are: (matches and teams)

matches table columns are; match_id match_date, home_team_id, away_team_id, home_score, away_score

teams table columns are; team_id, team_name

I want to see below result :

One team’s Home and Away score in one table as

Example: Team is Real Madrid

Results:

CodePudding user response：

You have to join two tables with teams table and matches table. Use OR paramater instead of AND.

CodePudding user response：

Use the same query, but test the team name against either h.team_name or a.team_name.

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
JOIN teams h ON h.team_id=m.home_team_id
JOIN teams a ON a.team_id=m.away_team_id
WHERE 'Real Madrid' IN (h.team_name, a.team_name) and m.league_id = 1;