13 Replies - 820 Views - Last Post: 20 April 2014 - 08:00 PM

#1 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 01:04 AM

I have 3 tables: Teams, Games, and Odds. The keys are:

Teams - TeamID, TeamName, TeamRecord

Games - GameID, FK TeamID, FK TeamID, GameDate, OddsID

Odds - OddsID, FK GameID, VisitorOdds, HomeOdds

So I have 2 foreign keys in the Games tables pointing to the same primary key column in the Teams table. I wan to be able to display both of the team names who are playing each other, I haven't figured that part out yet

Is This A Good Question/Topic? 0
  • +

Replies To: Best Practice for Multiple Foreign Keys to Same Table

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13766
  • View blog
  • Posts: 54,945
  • Joined: 12-June 08

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 06:54 AM

You would be selecting from the 'games' table with one join to the 'teams' table for the first team name, and then a second join to that same table for the second team's name.
Was This Post Helpful? 1
  • +
  • -

#3 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 07:15 AM

I would change the column name in the games table. TeamID and TeamID should be something more along the lines of home_team and visiting_team.

What does the TeamRecord column hold? Do you have anywhere you are storing the winner of the games?
Was This Post Helpful? 0
  • +
  • -

#4 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 08:36 AM

View Postmodi123_1, on 20 April 2014 - 06:54 AM, said:

You would be selecting from the 'games' table with one join to the 'teams' table for the first team name, and then a second join to that same table for the second team's name.



Are you sure that I'm able to join to the same table twice? I think I tried that and it didn't work...

View Postastonecipher, on 20 April 2014 - 07:15 AM, said:

I would change the column name in the games table. TeamID and TeamID should be something more along the lines of home_team and visiting_team.

What does the TeamRecord column hold? Do you have anywhere you are storing the winner of the games?



Thanks I've made that change to those columns, I've since removed the TeamRecord column as well...
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13766
  • View blog
  • Posts: 54,945
  • Joined: 12-June 08

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:03 AM

Yes.. I do it routinely with 'decode' tables.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:22 AM

What is a decode table? Encryption where it holds the key?
Was This Post Helpful? 0
  • +
  • -

#7 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:24 AM

View Postmodi123_1, on 20 April 2014 - 11:03 AM, said:

Yes.. I do it routinely with 'decode' tables.


SELECT tbTeam.TeamName AS [Visitor], tbTeam.TeamName AS [Home], CONVERT(VARCHAR,tbGame.GameDate,107) AS [Date]
FROM tbTeam
JOIN tbGame
ON tbTeam.TeamID=tbGame.VisitorTeamID
JOIN tbGame
ON tbTeam.TeamID=tbGame.HomeTeamID

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13766
  • View blog
  • Posts: 54,945
  • Joined: 12-June 08

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:26 AM

@astone - decode as in if there are types and categories for a row I store a numeric enumeration, but keep the number to word stored in another table to reference in a join.

@high - I am not sure what the intent of posting a chunk of code with no explanation or other words, but let's roll the dice and "yes.. you need to give them aliases".
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:28 AM

Makes sense. Didn't mean to hijack
Was This Post Helpful? 0
  • +
  • -

#10 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 11:43 AM

View Postmodi123_1, on 20 April 2014 - 11:26 AM, said:

@astone - decode as in if there are types and categories for a row I store a numeric enumeration, but keep the number to word stored in another table to reference in a join.

@high - I am not sure what the intent of posting a chunk of code with no explanation or other words, but let's roll the dice and "yes.. you need to give them aliases".


I tried the following select statement, no errors but 0 rows affected, resulting table is blank...

SELECT t.TeamName AS [Visitor], t.TeamName AS [Home], CONVERT(VARCHAR,g.GameDate,107) AS [Date]
FROM tbTeam t
JOIN tbGame g
ON t.TeamID=g.VisitorTeamID
JOIN tbTeam
ON t.TeamID=g.HomeTeamID

Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13766
  • View blog
  • Posts: 54,945
  • Joined: 12-June 08

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 01:23 PM

That would not be the series of 'from' and 'joins' I described in my other post.
Was This Post Helpful? 0
  • +
  • -

#12 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 06:35 PM

The following code seem to have worked. How do I set this thread to 'solved'?

SELECT t1.TeamName AS [Visitor], t2.TeamName AS [Home], CONVERT(VARCHAR,g.GameDate,107) AS [Date]
FROM tbGame g
INNER JOIN  tbTeam t1
ON t1.TeamID=g.VisitorTeamID
INNER JOIN tbTeam t2
ON t2.TeamID=g.HomeTeamID

Was This Post Helpful? 0
  • +
  • -

#13 higherprimate  Icon User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 06:40 PM

How would I start creating stored procedures to add/delete/update games and then connect it to a windows form (all while error checking)?
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13766
  • View blog
  • Posts: 54,945
  • Joined: 12-June 08

Re: Best Practice for Multiple Foreign Keys to Same Table

Posted 20 April 2014 - 08:00 PM

No clue on the specific language not given, but if it were, say c#, then something like this:

http://msdn.microsof...y/d7125bke.aspx
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1