Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2006
    Posts
    6

    Unanswered: 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your table layouts, and if possible, a few rows of sample data from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    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.

  9. #9
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    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?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, do create indexes on foreign keys

    and yes, they can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •