SQL join of 3 tables

Graze

Senior member
Nov 27, 2012
468
1
0
I need to join three tables two of which have matching field I am using for that join and the last table has a field that matches that of the first joined table.

I can successfully join the first two table but can't seem to join the third. My query is below. Can anyone offer a suggestion as to why the join isn't working?


SELECT Athlete.Ath_no, Athlete.Last_name, Athlete.First_name, Team.Team_abbr FROM Athlete INNER JOIN Team ON Team.Team_no=Athlete.Team_no INNER JOIN Entry ON Athlete.Ath_no=Entry.Ath_no


Tables and revelant fields

Athlete table
Fields: Ath_no, Team_no

Team table
Fields: Team_no

Entry table
Fields: Ath_no
 

pauldun170

Diamond Member
Sep 26, 2011
9,146
5,095
136
Whats the relationships between the tables?
Team has many athletes
Athlete has one team
What is "entry" table?
 

Train

Lifer
Jun 22, 2000
13,579
75
91
www.bing.com
at first glance your query looks ok, are you getting an error message, or just not the reslts you want?

I'm guessing you either don't need the entry table, or need to add some fields from the entry table to your list of columns
 

Graze

Senior member
Nov 27, 2012
468
1
0
Whats the relationships between the tables?
Team has many athletes
Athlete has one team
What is "entry" table?

I actually need to join four tables but that wasn't working so I tried three and that didnt work either.
For simplicity sake I left out the join with the forth table since the join with three tables yielded basically the same error I figured once I sorted the three table join then the forth would come off without a hitch.

The Entry table would used for the forth join with an Event table

I was getting an ODBC error in PHP. I am reading from an Access file that was produced by another program.

Seems like Access required some weird parenthesis.
"SELECT
Athlete.[Ath_no]
,Athlete.[Last_name]
,Athlete.[First_name]
,Team.[Team_abbr]
FROM
((Athlete
INNER JOIN Team
ON Team.[Team_no] = Athlete.[Team_no])
INNER JOIN Entry
ON Entry.[Ath_no] = Athlete.[Ath_no])";

Thanks for taking a look guys.