Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Correct syntax for inner join on multiple tables

    Hi,

    Can some body tell me what the correct syntax is for an inner join on multiple tables?

    I am trying to run the following query in Microsoft Access but it keeps coming up with syntax error.

    Code:
    SELECT JS.JobseekerID
             , JS.FirstName
             , JS.LastName
             , JS.Currency
             , JS.AccountRegDate
             , JS.LastUpdated
             , JS.NoticePeriod
             , JS.Availability
             , C.CountryName
             , S.SalaryAmount
             , DD.DisciplineName
             , DT.DegreeLevel 
        FROM Jobseekers JS 
    INNER 
       JOIN Countries C 
          ON JS.CountryID = C.CountryID 
    INNER 
       JOIN SalaryBracket S 
          ON JS.MinSalaryID = S.SalaryID 
    INNER 
      JOIN DegreeDisciplines DD 
         ON JS.DegreeDisciplineID = DD.DisciplineID 
    INNER 
      JOIN DegreeType DT 
         ON JS.DegreeTypeID = DT.DegreeTypeID 
    WHERE
      JS.ShowCV = 'Yes'
    Does Access use different sql syntax or something?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Access likes you to use parantheses for some reason...
    Code:
     FROM (((Jobseekers JS 
    INNER 
       JOIN Countries C 
          ON JS.CountryID = C.CountryID) 
    INNER 
       JOIN SalaryBracket S 
          ON JS.MinSalaryID = S.SalaryID) 
    INNER 
      JOIN DegreeDisciplines DD 
         ON JS.DegreeDisciplineID = DD.DisciplineID) 
    INNER 
      JOIN DegreeType DT 
         ON JS.DegreeTypeID = DT.DegreeTypeID
    I think it's looking for something like that.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Why do I even need to use the inner join statement in an SQL query when I can acheive the same by using the AND operator as follows:

    Code:
    SELECT JS.JobseekerID
             , JS.FirstName
             , JS.LastName
             , JS.Currency
             , JS.AccountRegDate
             , JS.LastUpdated
             , JS.NoticePeriod
             , JS.Availability
             , C.CountryName
             , S.SalaryAmount
             , DD.DisciplineName
             , DT.DegreeLevel 
        FROM Jobseekers JS, Countries C, SalaryBracket S, DegreeDisciplines DD
             , DegreeType DT
        WHERE
               JS.CountryID = C.CountryID 
               AND JS.MinSalaryID = S.SalaryID 
               AND JS.DegreeDisciplineID = DD.DisciplineID 
               AND JS.DegreeTypeID = DT.DegreeTypeID 
               AND  JS.ShowCV = 'Yes'

  4. #4
    Join Date
    Apr 2004
    Posts
    4

    Question What about Cross Join and Left Join together??

    how is the syntax if cross Join and left join together....?

    such as

    select *
    FROM Organziation
    CROSS JOIN Item
    LEFT JOIN (
    SELECT *
    FROM Event, Program
    WHERE Event.ID = Program.EventID
    AND Event.Accepted = true
    ) AS List ON List.OrganizationID = OrganizationID

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii
    Why do I even need to use the inner join statement in an SQL query when I can acheive the same by using the AND operator as follows:
    because JOIN syntax is significantly easier to understand

    furthermore, it is a simple matter to turn an INNER JOIN into a LEFT OUTER JOIN when you have to, whereas with the comma-style joins (where the tables are listed in the FROM clause, and the join conditions specified in the WHERE clause), this is quite problematic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2010
    Posts
    8
    You can represent the outer joins with an asterisk. Such as table1.Key =* table2.ForeignKey

Posting Permissions

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