| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-11-06, 11:51
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
|
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 
|
|
04-11-06, 12:38
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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
|
|
04-11-06, 13:01
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
|
sorry, here's the error and it's not returning anything
|
|
in SQL Analyzer (a freebie) i get a dialog that says Error Number: -2147217900
|
|
04-11-06, 13:06
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
please show your table layouts, and if possible, a few rows of sample data from each table
|
|
04-11-06, 13:27
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Jet (the database engine that underlies Microsoft Access and other tools) can be charitably described as tempermental. Does it work if you use:
Code:
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
|
|
04-11-06, 13:56
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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
|
|
04-11-06, 14:03
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
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
|
|
04-12-06, 10:48
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
|
Sorry for the delay, here's a sample of data structure
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.
|
|
04-12-06, 10:54
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
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 
|
|
04-12-06, 10:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Code:
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'
|
|
04-12-06, 11:04
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
|
r937..YOU ROCK!!
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?
|
|
04-12-06, 11:17
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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
|
|
04-12-06, 11:28
|
|
Registered User
|
|
Join Date: Apr 2006
Posts: 6
|
|
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?
|
|
04-12-06, 11:30
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
yes, do create indexes on foreign keys
and yes, they can
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
HELP w/ multiple table join on foreign keys
