I need some help with an SQL query. I will try to explain as best I can what the task is suppose to do. We have two relations, Game and Matchteam (The database is of course much bigger, but for this query is enough with these two relations). The relation Game contains the attributes ("PK" mid, arena, city, country) and the relation Matchteam contains the attributes ("FK" mid, teamname, city, country).
mid arena city country
1 Camp Nou Barcelona Spain
2 Old Trafford Manchester England
3 Craven Cottage London England
4 Camp Nou Barcelona Spain
The objective is to find information on the arenas where only teams from the same country have played. Every game consists of two match teams from the relation Matchteam.
mid teamname city country
1 FC Barcelona Barcelona Spain
1 Real Madrid Madrid Spain
2 Man Utd Manchester England
2 Man City Manchester England
3 Fulham London England
3 Juventus Turin Italy
4 FC Barcelond Barcelona Spain
4 Chelsea FC London England
SELECT DISTINCT arena, city, country
WHERE mid NOT IN (SELECT mid FROM Matchteam
WHERE country NOT IN (SELECT country FROM Matchteam
WHERE Matchteam.country = Game.country));
If I use this query I get this result:
The result might look right at first glance, but the first row should not appear. That is, (Camp Nou, Barcelona, Spain). Because the match with mid 4 contains an English team and should therefore not be included in the result even though the match with mid 1 is played by two Spanish team.
arena city country
Camp Nou Barcelona Spain
Old Trafford Manchester England
Unfortunately I can't think of any easy way to solve this dilemma, I have tried several different approaches but without any success so far. I would be very grateful if anyone knows how to easily solve this problem.
Thanks in advance!
PS: The database is in Access 2003.