Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Unanswered: Simple question, I guess...

    Hi there!

    I'm having a problem to achieve the best solution to this:

    I'll give one example:

    1 table: Teams (teamID, teamName)
    1 table: GamesCalendar (calendarID, teamID1, teamID2, Date)

    table GamesCalendar has to have 2 foreign keys referencing each team that will play against each other...

    Do I have to duplicate table Teams (not great solution...not sure)?

    I'm getting a little bit confused sorry...

    thank you for helping!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the GamesCalendar has to have 2 FKs to Teams

    what's the problem with that? that's the correct way to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This can be a messy requirement to implement. By having two FKs in the Games table you will need to create some UNION views or apply conditional logic to your queries to make sure you join Team to both applicable FKs in Game.
    An alternative is to create a third table called "GameTeams" to implement a many to many relationship between Games and Teams. This may simplify some of your queries, but you will have to enforce a rule requiring that a game have no more than two associated team records.
    So both methods have some drawbacks, and you'll just have to work around whichever one you choose.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2007
    Posts
    3
    Exactly, that's what I've done, but when I need some return from the database, it gives me nothing...

    doing something like this:

    SELECT Calendar.teamID1, Calendar.teamID2, Calendar.Date
    FROM Calendar INNER JOIN
    Teams ON Calendar.teamID1 = Teams.teamID AND Calendar.teamID2 = Teams.teamID

    in one case Teams.teamID will be 1 (team A) and in other Teams.teamID will have to be 2 (team B), isn't it?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your query would only return games where a team played itself. So if you are storing scrimages then you are set, but otherwise try this:
    Code:
    SELECT Calendar.teamID1, Calendar.teamID2, Calendar.Date
    FROM Calendar INNER JOIN
    Teams ON Calendar.teamID1 = Teams.teamID OR Calendar.teamID2 = Teams.teamID
    This is exactly what I meant when I said you will need to apply conditional logic in your queries.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    conditional, schmonditional!!
    Code:
    SELECT Calendar.Date
         , Team1.teamName as Team1Name
         , Team2.teamName as Team2Name
      FROM GamesCalendar 
    INNER 
      JOIN Teams AS Team1
        ON Team1.teamID = Calendar.teamID1
    INNER 
      JOIN Teams AS Team2
        ON Team2.teamID = Calendar.teamID2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Schmonditional!!"??? It dependsch on what he wantsch to dischplay, I schuppose.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2007
    Posts
    3
    hey guys...thank you very much for your help!

    what r937 posted solved my problem nicely and also helped me to understand better some issues...

    thank you all, again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •