Correct syntax for inner join on multiple tables

ozzii · #1 (**permalink**) 04-19-07, 14:46

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?

RedNeckGeek · #2 (**permalink**) 04-19-07, 15:12

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.

ozzii · #3 (**permalink**) 04-20-07, 09:53

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'

geossl · #4 (**permalink**) 10-09-09, 05:08

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

r937 · #5 (**permalink**) 10-09-09, 06:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech