SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table next (
  clubhome varchar(255),
  clubaway varchar(255)
);
✓
create table results (
  clubhome varchar(255),
  clubaway varchar(255),
  scorehome int,
  scoreaway int
);
✓
insert into next values
('Chicago', 'New York'), ('Pittsburgh', 'Norway'), ('Arizona', 'Grand Dutchy Fenwick');
✓
insert into results values
  ('Chicago', 'New York', 2, 5),
  ('New York', 'Chicago', 3, 7),
  ('Pittsburgh', 'Seattle', 0, 9);
✓
select *
from next n
left join results r on n.clubhome in (r.clubhome, r.clubaway)


clubhome
clubaway
clubhome
clubaway
scorehome
scoreaway


Chicago
New York
Chicago
New York
2
5

Chicago
New York
New York
Chicago
3
7

Pittsburgh
Norway
Pittsburgh
Seattle
0
9

Arizona
Grand Dutchy Fenwick
null
null
null
null


select
  n.clubhome,
  case
  when n.clubhome = r.clubhome then r.scorehome
  when n.clubhome = r.clubaway then r.scoreaway
  end as score
from next n
left join results r on n.clubhome in (r.clubhome, r.clubaway)


clubhome
score


Chicago
2

Chicago
7

Pittsburgh
0

Arizona
null


select
  n.clubhome,
  group_concat(
    case
    when n.clubhome = r.clubhome then r.scorehome
    when n.clubhome = r.clubaway then r.scoreaway
    end
  ) as score
from next n
left join results r on n.clubhome in (r.clubhome, r.clubaway)
group by n.clubhome


clubhome
score


Arizona
null

Chicago
2,7

Pittsburgh
0

clubhome	clubaway	clubhome	clubaway	scorehome	scoreaway
Chicago	New York	Chicago	New York	2	5
Chicago	New York	New York	Chicago	3	7
Pittsburgh	Norway	Pittsburgh	Seattle	0	9
Arizona	Grand Dutchy Fenwick	null	null	null	null