HELP w/ multiple table join on foreign keys

I'm scratching my head, not a sql expert so i need help w/ the following:

i have three tables in Access:
airportNames, facililtyID, facilityNames

i'm using facilityID as the intermediate table to join above tables.
i'm passing a value to the WHERE clause in my sql statement comparing airportNames.strCode.

Here's the sql that doesn't work, it generates an error:

SELECT facilityNames.strName, airportNames.strName, airportNames.strCode FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames)
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames)
WHERE airportNames.strCode = 'ATL'

I know this can be done but i can't get it to work:eek:what does "can't get it to work" mean? does your query cause the server to crash? does the query actually run? if not, does it produce an error message? if so, what is the error message? if it runs, does it return any rows? no rows? the wrong rows?

please show your table layouts, and if possible, a few rows of sample data from each table|||in SQL Analyzer (a freebie) i get a dialog that says Error Number: -2147217900|||please show your table layouts, and if possible, a few rows of sample data from each table|||Jet (the database engine that underlies Microsoft Access and other tools) can be charitably described as tempermental. Does it work if you use:SELECT facilityNames.strName, airportNames.strName, airportNames.strCode
FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames))
WHERE airportNames.strCode = 'ATL'-PatP|||pat, you're a genius

but it's probably not jet being finicky, it's probably that we have our table and column names bacwards

there is no table called facilityID, that's a column used to join tables

his syntax is all wrong, and i tried to fix it and got confused

i cannot guess which columns the tables should be joined on without some more info|||I was just working from the original posting... I don't have a clue what they've really got, but at least what they posted was consistent even if it may not have been correct.

-PatP|||Here's what the three tables involved look like in structure and data:

Table airportNames

IDAirportNames | strName | strCode
1 | Hartsfield | ATL
2 | Birmingham | BHM

Table facilityID

IDFacility | IDFacilityNames | IDAirportNames
1 | 1 | 1
2 | 0 | 1

Table facilityNames

IDFacilityNames | strName
1 | XYZ facility

Does this help?? FYI, I'm using access 2000.|||Pat Phelan, tried your query but i still get an error. man, either access is a pos, this sql analyzer thing is a pos or both. i need to get this thing working:(|||SELECT facilityNames.strName
, airportNames.strName
, airportNames.strCode
FROM (
airportNames
INNER
JOIN facilityID
on facilityID.IDAirportNames
= airportNames.IDAirportNames
)
INNER
JOIN facilityNames
ON facilityNames.IDFacilityNames
= facilityID.IDFacilityNames
WHERE airportNames.strCode = 'ATL'|||dude, i've been sweating this for a couple of days, thanks for you time on this, if you're ever doing any Flash Actionscript work and need some help, i'm all ears...paul at mediacurrent dot com.

suffice it to say that your sql statement returned exactly what i'd been looking for. now i need to do a full data migration and see what happens...stay tuned. BTW, can i use the INNER JOIN statement in SQL Server or does it only accept JOIN?|||INNER is an optional keyword, JOIN == INNER JOIN

if you migrate this to sql server, you may want to remove the microsoft access parentheses from the FROM clause

flash actionscript? thanks for the offer, but (a) i'm not quite ready to tackle flash yet, and (b) i cannot think of anything that i'd use it for|||one more question, i've read some stuff on indexing foreign keys for better join performance. would you recommend this? can a foreign key be indexed if duplicate keys exist in the column?|||yes, do create indexes on foreign keys

and yes, they can