Can't get a table to join in multiple fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can't get a table to join in multiple fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-17 : 13:03:48

Matt Meadows writes "what i'm trying to do is use a table field in one table and join it in multiple fields in a second table.....case in point would be this situation i have a table with nfl teams...there is a teamid and a team name. in another table i have the nfl season listed (simplified). the nfl season table contains the weekid (ie what weekname we're in), team1 and team2...what i can't figure out is how to grab the teams.teamname for each of the id's listed in the season table. it either returns no rows (like select picks.* teams.teamname, weeks.weekname from picks, weeks, teams where picks.week=week.weekid and picks.team1=teams.teamid and picks.team2=teams.teamid)or i get syntax errors. is this a design issue or is there a join i can use to bring it together in a logical manner?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 13:10:41

I believe you need to join to the teams table twice and alias them to different names. Of course, without you actual DDL, some sample data, and you desired rowset, my suggestion is really just a shot in the dark...

Jay White
{0}

r937
Posting Yak Master

112 Posts

Posted - 2002-09-17 : 17:24:12

jay's right, you have to join it twice

here's an actual sample from my nfl pool --

SELECT games.gamedate
     , games.gameno
     , away.teamcity   AS visitor 
     , games.spread
     , home.teamcity   AS hometeam
  FROM ( games
  INNER JOIN teams AS away 
       ON games.visitor = away.teamabbr )
  INNER JOIN teams AS home
       ON games.hometeam = home.teamabbr
 WHERE games.weekno =  weeknumber
ORDER BY gameno

my pool is available only to registered participants, but i'd be happy to share the sql queries if you wish

rudy
http://rudy.ca/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources