how to combine results to get number of teams against whom Arsenal won all the available six points

I have a table named ‘home’ that is storing Arsenal football club’s performance in the league at home in 2003-04 season, while the table ‘away’ stores Arsenal’s performance away in the same season.

Table `home`:

Opponent           Goal_Scored      Goal_Conceded
Everton                2                 1
Aston Villa            2                 0
Portsmouth             1                 1
Newcastle United       3                 2
Chelsea                2                 1
Tottenham Hotspur      2                 1
Fulham                 0                 0
Blackburn Rovers       1                 0
Wolverhampton Wonderers 3                0
Middlesbrough          4                 1


Table `away`:

Opponent           Goal_Scored      Goal_Conceded
Everton                1                 2
Aston Villa            2                 2
Portsmouth             1                 2
Newcastle United       2                 3
Chelsea                2                 2
Tottenham Hotspur      2                 3
Fulham                 2                 0
Blackburn Rovers       1                 2
Wolverhampton Wonderers 3                2
Middlesbrough          1                 4

Note that a team is awarded three points for a win, one for a draw and zero for a loss.

I wanted to determine the number of teams against whom Arsenal won all the available six points.

And for that purpose, I have written a partial code:

CREATE TABLE home (
    Opponent varchar(60),
    Goal_Scored int(8),
    Goal_Conceded int(8)
);

CREATE TABLE away (
    Opponent varchar(60),
    Goal_Scored int(8),
    Goal_Conceded int(8)
);

INSERT INTO home (Opponent, Goal_Scored, Goal_Conceded)
VALUES 
("Everton",2,1),
("Aston Villa",2,0),
("Portsmouth",1,1),
("Newcastle United",3,2),
("Chelsea",2,1),
("Tottenham Hotspur",2,1),
("Fulham",0,0),
("Blackburn Rovers",1,0),
("Wolverhampton Wonderers",3,0),
("Middlesbrough",4,1);


INSERT INTO away (Opponent, Goal_Scored, Goal_Conceded)
VALUES
("Everton",1,2),
("Aston Villa",2,2),
("Portsmouth",1,2),
("Newcastle United",2,3),
("Chelsea",2,2),
("Tottenham Hotspur",2,3),
("Fulham",2,0),
("Blackburn Rovers",1,2),
("Wolverhampton Wonderers",3,2),
("Middlesbrough",1,4);

and to get the combined result, I tried the below code but it did not produce the expected result:

select a.opponent
from home a 
where a.goals_scored > a.goals_conceded
union all
select b.opponent
from away b 
where b.goals_scored > b.goals_conceded

But then how I should combine the results to get number of teams against whom Arsenal won all the available six points

Answer

Since you already have a solution here’s the join solution:

select opponent
from home
join away using (opponent)
where home.goal_scored > home.goal_conceded
  and away.goal_scored > away.goal_conceded;

where we join the home and away records for each team and then filter out anything where Arsenal didn’t win both legs.