views:

60

answers:

3

Are ordinary sql queries different from MS Access 2007 queries?

I have this simple query but it is not working in MS Access query (SQL View). What is wrong with this?

SELECT StudentSubject.*, Subject.*,Professor.*, Student.*, Church.*
FROM 
(

SELECT StudentSubject.*, Subject.*,Professor.* , Student.*
FROM 
 (

 SELECT  StudentSubject.*, Subject.*,Professor.* 
FROM
 StudentSubject 
LEFT JOIN Subject ON StudentSubject.SubjectID=Subject.SubjectID
 INNER JOIN Professor ON Subject.ProfessorID=Professor.ProfessorID

  ) 
  INNER JOIN Student ON StudentSubject.StudentID=Student.StudentID
) 
LEFT JOIN Church ON Student.ChurchID=Church.ChurchID;

I believe this would work if I was using MySQL/MSSQL(If I use alias and do it directly - this is the ouput of ms access sql designer)

I get syntax error on join operation. But MS Access doesn't point out which join.

+2  A: 

It is slightly different, but I don't think the query in the question would work on other DBs, either - there are references to table names in subqueries that aren't aliased with the matching table name.

Try this, instead:

SELECT StudentSubject.*, Subject.*,Professor.*, Student.*, Church.*
FROM  StudentSubject 
LEFT JOIN Subject ON StudentSubject.SubjectID=Subject.SubjectID
INNER JOIN Professor ON Subject.ProfessorID=Professor.ProfessorID
INNER JOIN Student ON StudentSubject.StudentID=Student.StudentID
LEFT JOIN Church ON Student.ChurchID=Church.ChurchID;

This may still return an error due to different columns having the same name - if so, you should replace .* above with only the required columns from each table, with appropriate column aliases.

Mark Bannister
@Mark you're right, I edited this straight from MS Access. I have already tried you're answer before I posted the question however I get a weird error. Syntax error (missing operator) 'StudentSubject.SubjectID=Subject.SubjectIDINNER JOIN Professor ON Subject.ProfessorID=Professor.ProfessorIDINNER JOIN Student ON StudentSubject.StudentID=Student.StudentIDLEFT JOIN Church ON Student.ChurchID=Church.Church'
geocine
@geocine, have you tried to replace `.*` with only the required columns from each table, with appropriate column aliases? Sometimes the returned error messages can be a bit misleading.
Mark Bannister
Yes, thank you. However, I found the answer.
geocine
+1  A: 

Adding the parenthesis did the trick

SELECT Subject.SubjectName,Professor.ProfessorName,Church.ChurchName,Student.StudentName 
FROM  ((((StudentSubject LEFT JOIN Subject 
ON StudentSubject.SubjectID=Subject.SubjectID)
INNER JOIN Professor ON Subject.ProfessorID=Professor.ProfessorID)
INNER JOIN Student ON StudentSubject.StudentID=Student.StudentID)
LEFT JOIN Church ON Student.ChurchID=Church.ChurchID);
geocine
This isn't the same query as you posted in your question. It may very well return the same data, but it is much, much less convoluted than your original, with all its nested derived-table subqueries.
David-W-Fenton
+1  A: 

Are ordinary sql queries different from MS Access 2007 queries?

By "ordinary SQL" you probably mean entry level SQL-92 Standard SQL (whether you know it or not!)

As a whole, Access (ACE, Jet, whatever) is not compliant with SQL-92.

Specifically, Access's JOIN syntax is not compliant with SQL-92.

In SQL-92, one or more JOIN clauses can be enclosed together within parentheses to show precedence; where no parentheses are used all JOIN clauses will have the same precedence.

In Access, each JOIN clause must be enclosed on its own within parentheses, however all JOIN clauses will have the same precedence.

In other words, Access chokes on the Standard syntax and its own syntax is less expressive than the Standard's :(

onedaywhen
Thank you for the very detailed answer
geocine