I'm trying to create two columns which are filtered based on a value from another table.
Here is my model:
In the Dim_Country table I would like to create two columns; HomeTeam and AwayTeam. Both of these values come from the Dim_Country tables Country field. The MatchRole field in the Link_CountryMatch table is what determines whether a country performs as a home team (MatchRole = 1) or an away team (MatchRole = 0) for a given match.
The SQL query looks like this:
SELECT m.MatchID, d.MatchDate, t.Tournament, MAX(h.Country) AS HomeCountry, MAX(a.Country) AS AwayCountry, m.HomeScore, m.AwayScore, m.Scoreline
FROM dbo.Fact_Match m
LEFT JOIN Dim_Tournament t ON m.Tournament_FK = t.TournamentID
LEFT JOIN Dim_Date d ON m.Date_FK = d.DateID
LEFT JOIN Link_CountryMatch l ON m.MatchID = l.Match_FK
LEFT JOIN Dim_Country h ON l.Country_FK = h.CountryID AND l.MatchRole = 1
LEFT JOIN Dim_Country a ON l.Country_FK = a.CountryID AND l.MatchRole = 0
GROUP BY m.MatchID, d.MatchDate, t.Tournament, m.HomeScore, m.AwayScore, m.Scoreline
ORDER BY MatchDate DESC, MatchID
And produces these results (sample):
MatchID MatchDate Tournament HomeCountry AwayCountry HomeScore AwayScore Scoreline 473843 2017-11-15 00:00:00.000 FIFA World Cup qualification Australia Honduras 3 1 2 473844 2017-11-15 00:00:00.000 FIFA World Cup qualification Peru New Zealand 2 0 2 473811 2017-11-14 00:00:00.000 FIFA World Cup qualification Burkina Faso Cape Verde 4 0 4 473812 2017-11-14 00:00:00.000 FIFA World Cup qualification Senegal South Africa 2 1 1 473813 2017-11-14 00:00:00.000 AFC Asian Cup qualification Macau Kyrgyzstan 3 4 1
I've tried playing around with FILTER and RELATED/RELATEDTABLE functions but haven't been able to replicate these results.