Thread: Need some help!

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2010
    Posts
    11
    Rep Power
    0

    Need some help!


    Hello!

    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).

    Relation Game:
    Code:
    mid    arena	       city	  country
     1   Camp Nou	     Barcelona	  Spain
     2   Old Trafford    Manchester	  England
     3   Craven Cottage  London	  England
     4   Camp Nou	     Barcelona	  Spain
    Relation Matchteam:
    Code:
    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
    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.

    SQL-query:
    SQL Code:
    SELECT DISTINCT arena, city, country
    FROM Game
    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:
    Code:
      arena          city      country 
    Camp Nou      Barcelona    Spain
    Old Trafford  Manchester   England
    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.

    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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2010
    Posts
    11
    Rep Power
    0

    Problem solved


    I eventually managed to solve the problem myself, here is the solution if anyone is interested.

    sql Code:
    SELECT arena, city, country
    FROM (SELECT arena, city, country, mid
          FROM Game
          WHERE mid NOT IN (SELECT mid FROM Matchteam
                            WHERE country NOT IN (SELECT country FROM Matchteam 
                                                  WHERE Matchteam.country = Game.country))) AS newRelation1
     
    GROUP BY arena, city, country
    HAVING COUNT (mid) = (SELECT amount
                          FROM (SELECT arena, COUNT (*) AS amount
                                FROM Game
                                GROUP BY arena) AS newRelation2
                          WHERE newRelation1.arena = newRelation2.arena);

IMN logo majestic logo threadwatch logo seochat tools logo