SQL join of 3 tables

[Grazehell]

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

 

[aL Mac]

I don't do much SQL where I work, so it is not jumping out at me, but it would help if you said what "isn't working". What happens?

 

[obs]

I'd look at the data and make sure the what your joining on is correct. There's nothing wrong with the SQL query from a syntax point of view so either the data or the schema of the tables must be different than what you're expecting. Check the types of Ath_no and Team_no in all 3 tables.

 

[Zangmonkey]

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]

Aside from the fact that you're not selecting anything from the "Entry" table... your query should work.

What results do you expect and what results are you seeing?

You're asking for Athletes and their team abbreviation, for athletes which have a team association and which have a matching value in the "Entry" table.

 

[Grazehell]

Ah yes nothing was selected from Entry but it will be, sorry I left that out.

I am using ODBC to query an MS Access file in php.

The error I am getting when the query in it's present state is

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Team.Team_abbr Entry.Event_ptr'., SQL state 37000 in SQLExecDirect in C:\wamp\www\Meet\sportlib.php on line 121

Its so weird that I have no issue with the query when I leave out the last join statement. I wonder if this is some ODBC issue now.

 

[BillNutz]

I believe in Access you need to use parenthesis to do multiple joins...

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]

something like that.

 

[mikeblas]

You don't want to use Access from PHP -- you need a multi-user DBMS.

 

[Grazehell]

I believe in Access you need to use parenthesis to do multiple joins...

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]

something like that.

You are right!
I corrected your query, seems like your last parenthesis needed to be moved to the end of the query, and that worked. I hate working with ODBC and Access!

"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])";

You don't want to use Access from PHP -- you need a multi-user DBMS.

...and I really don't want to either but this is for read only access for presentation purposes.
I am querying an MS file that is created by a sports meet program.

I would never use Access for any real work if I could help it.

Thanks folks!