Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7

    Unanswered: INNER JOIN syntax?

    QUESTION: What is the syntax for multiple INNER JOINs? One INNER JOIN is a self-join. Most importantly (I think), in what order should I reference the primary table (twice) and the lookup table?

    MS Access 2003 wants to force a certain syntax in my SQL statement that causes it to fail. My SQL works the first time before Access can do its dirty deed.

    I am referencing two tables with three attaches (FROM and two INNER JOINs). In the self-join INNER JOIN, I use an alias with AS.

    The primary table, Employees, lists both employees and supervisors, easily distinguished by the Title field. The look up table, Places, lists the address and phone number of the place where the team will work on a given day.

    I need to generate a letter to each employee that shows:
    - Employee name and address
    - Place name, address and phone number
    - Supervisor name and phone number

    The Access Query wizard does not produce the results I want, so I have gone directly to the SQL page.

    =========
    My first SQL (fields simplified, all text type, including key fields):

    SELECT DISTINCT Employees.Name, Employees.Address, Chiefs.Name, Chiefs.Phone, Places.PlaceID, Places.Name, Places.Address
    FROM Employees
    INNER JOIN Employees AS Chiefs ON ((Employees.PlaceID=Chiefs.PlaceID) AND (Chiefs.Title="Super"))
    INNER JOIN Places ON Employees.PlaceID=Places.PlaceID
    WHERE Employees.PlaceID = "301" OR Employees.PlaceID = "302"
    ORDER BY Employees.Name

    ==========
    My second SQL (same statements, but Chiefs INNER JOIN is embedded inside the Places INNER JOIN). This works more often.

    SELECT DISTINCT Employees.Name, Employees.Address, Chiefs.Name, Chiefs.Phone, Places.PlaceID, Places.Name, Places.Address
    FROM Employees
    INNER JOIN Places
    (INNER JOIN Employees AS Chiefs ON ((Employees.PlaceID=Chiefs.PlaceID) AND (Chiefs.Title="Super"))) ON Employees.PlaceID=Places.PlaceID
    WHERE Employees.PlaceID = "301" OR Employees.PlaceID = "302"
    ORDER BY Employees.Name

    ===========
    MS Access rewrites the SQL statement, putting Places in the FROM and Employees in the inside INNER JOIN. And it doesn't work. It generates an error saying the FROM has a syntax error.

    When I execute my second SQL statement, it works every time -- once. Then Access has it way.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Why are you inner joining the same table on its self? (I believe I know why, but I want to make sure my thought process is correct)

    Why are you including a criteria as part of the join? (Chiefs.Title="Super" should be part of the "Where")

    S-

  3. #3
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7
    Thanks for replying, S-

    ===== You asked:
    Why are you inner joining the same table on its self? (I believe I know why, but I want to make sure my thought process is correct)

    Why are you including a criteria as part of the join? (Chiefs.Title="Super" should be part of the "Where")
    =====

    I am new to multiple table SQL statements. I am comfortable with simple SELECT queries, including embedded statements for recordsets in VBA.

    I am attaching the same table twice, thinking I needed to use INNER JOIN so I can get the Chief's name (the supervisor) for each worker. I thought I needed an INNER JOIN because I needed an alias. I am referencing the same fields for Chief and worker (lastName, firstName, etc), so I thought the alias would sort them out.

    I added the Chiefs.Title criteria to the join ON because I thought that's where it belonged. That is, it's qualifying the INNER JOIN table, not the FROM table.

    On the web site, DevGuru, I just read that FROM can list multiple tables. Does that apply to MS Access, too? And will it let me list the same table twice, using AS?

    The example I read on DevGuru used FROM exclusively. It did not call INNER JOIN. It looked much simpler to read.

    I got into using INNER JOIN because the SQL wizard put it there, so I thought that was the desired format. I resorted to building the SQL statement manually because I couldn't get the wizard to behave (I might have been too pigheaded).

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    I have never used FROM and I don't know if it will work.

    Lets try this.

    Post some sample data

    Tell us what you are after

    post what you want the results to look like

    and we will see what we can get you.


    Off the top of my head, I don't think you want to use an inner join, I think you need a nested select statement like the following

    Select *, (Select Employee.Name From Employee Where Employee.PlaceID = E.PlaceID and Employee.Title='Super') as SupName
    from Employees AS E
    Where E.PlaceID = "301" OR E.PlaceID = "302"
    ORDER BY E.Name

    Don't know if it is completely right, but you should get the jist of it. If you can get this to work, send me the data I requested

    S-

  5. #5
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7
    Thank you, S-

    I will take this to my client's office later today and try your solution. I also will grab a copy of my actual SQL statement.

    The FROM clause I refer to is the same one you are using in your recommendation here. Sorry I wasn't clear.

    QUESTION: Your solution gives me the Chiefs data out of the Employees table along with the workers. But it does not pull in the Places lookup table. Would I embed another (Select ...) statement, similar to the one you wrote but without the alias, at the end of your (Select ...)? As in

    Select *,(Select ...)(Select ...) ...

    I appreciate your time.

    Dan
    ===============================

    I have never used FROM and I don't know if it will work.

    ...

    Off the top of my head, I don't think you want to use an inner join, I think you need a nested select statement like the following

    Select *, (Select Employee.Name From Employee Where Employee.PlaceID = E.PlaceID and Employee.Title='Super') as SupName
    from Employees AS E
    Where E.PlaceID = "301" OR E.PlaceID = "302"
    ORDER BY E.Name

    ...
    S-

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    Select E.*, Places.PlaceID, Places.Name, Places.Address,
    (Select Employee.Name From Employee Where Employee.PlaceID = E.PlaceID and Employee.Title='Super') as SupName
    from Employees AS E INNER JOIN Places ON E.PlaceID=Places.PlaceID
    Where E.PlaceID = "301" OR E.PlaceID = "302"
    ORDER BY E.Name

    This is what I would use
    S-

  7. #7
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7
    =====
    Post some sample data
    Tell us what you are after
    post what you want the results to look like
    and we will see what we can get you.
    =====

    The Employees table has the usual stuff: lastName, firstName, address, city, and so forth. It also refers to the Places table in its Places field, referencing the Places ID number (Employees.placeNumber), which is the foreign key and is text. The Places lookup table has the placeName, placeAddress, and other such information.

    The goal is an MS Word merged field letter to each worker.

    "Hello Staff,

    (... some preamble and welcome ...)

    Your assignment is ...

    <<Employees.firstName>> <<Employees.lastName>>
    <<Employees.address>>
    <<Employees.city>> ... etc

    <<Places.placeNumber>> <<Places.placeName>>
    <<Places.address>>
    <<Places.city>> <<Places.zip>>

    <<Chiefs.firstName>> <<Chiefs.lastName>>
    <<Chiefs.phone>>

    I have no problem finding a query or pulling in the appropriate fields as I show above. All my problems are around getting the query to work.

    Thank you,
    Dan

  8. #8
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7
    ====
    Select E.*, Places.PlaceID, Places.Name, Places.Address,
    (Select Employee.Name From Employee Where Employee.PlaceID = E.PlaceID and Employee.Title='Super') as SupName
    from Employees AS E INNER JOIN Places ON E.PlaceID=Places.PlaceID
    Where E.PlaceID = "301" OR E.PlaceID = "302"
    ORDER BY E.Name

    This is what I would use
    S-
    ====

    That looks like it will work, S-!

    QUESTION: I notice a single quote around Super and double quotes around 301 and 302. Is that significant, or am I being thick?

    Dan

  9. #9
    Join Date
    Nov 2003
    Posts
    267
    I just use singles quotes instead of double, I copied and posted the part with double.

    S-

  10. #10
    Join Date
    Mar 2004
    Location
    Reston, Virginia
    Posts
    7
    Well, I solved it, and ultimately, the solution was wonderfully simple:

    SELECT DISTINCT Emp.*,Chf.FirstName,Chf.LastName,Chf.Phone,
    Plc.Number,Plc.Name,Plc.Address
    FROM Employees AS Emp, Employees AS Chf, Places AS Plc
    WHERE ..............

    The WHERE stuff was straightforward -- surprisingly so.

    The crucial piece was the fact that the FROM clause can take multiple table names and, if necessary, aliases. I didn't know it could to that.

    QUESTION: Why would one use INNER JOIN? SELECT DISTINCT solves the problem of mulitple or duplicate records -- I think that applies here.

    A friend just told me that INNER JOIN is specific to MS Access. If so, I would prefer not to use it. I want my Access applications to migrate to more powerful engines if the applications start getting high use.

    Thank you for your help and valuable time.
    Dan

Posting Permissions

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