Better "Join" vs "Where" clause?

Michel Walsh wrote:
Outer join with a condition in the on clause implying only one table.
The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID AND
authors.City = 'Iqaluit'



as example. With Jet 3.5, you got no record, but with MS SQL Server,
you get all records from authors.


Interesting. All of my attempts to use that syntax in Jet had failed
(syntax error), forcing me to resort to using a subquery to get that effect:

.... LEFT JOIN (select authorid from books where City = 'Iqaluit') as q
ON authors.authorID = q.authorID

Are you sure your example statement doesn't raise an error in Jet?



There is also the case of update through joins, which is immediately
available with JET, but with proprietary syntax for MS SQL Server (or
explicitly over a view).

I know about the different update/delete syntax - I thought we were
discussing the join syntax itself ... in select statements. David? is this
what you were referring to? If so, I withdraw my objection to your statement
except to say that it isn't the join syntax per se that is causing the
problems in this case: it's the different syntax required for update/delete
statements in T-SQL.



Not linked to join, but you cannot use ALIAS over alias in MS SQL
Server, while you can in Jet:

SELECT price * 1.06 AS TPS, (price + TPS) * 1.045 AS PST, price
+TPS + PST AS totalPrice FROM somewhere



I always thought that worked in Access because Access rewrote the query
before passing it to Jet. I'll have to try executing a similar sql statement
via ADO to see if that's the case.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"