Re: Better "Join" vs "Where" clause?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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"


.

Relevant Pages

  • Re: HELP! UPDATE syntax that work in sql server and access vba
    ... When you are working with Access ODBC linked tables, you must use the JET ... You should ask in a newsgroup about queries under Access how to ... In sql server I use syntax as follow: ... I also tried the following syntax in access query: ...
    (microsoft.public.access.adp.sqlserver)
  • Re: CREATE TEMPORARY TABLE
    ... suggestions that this syntax relates to SQL Server or an ADP. ... CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet ... for non-Jet engine databases. ... The syntax includes WITH COMPRESSION which only applies to Jet ...
    (microsoft.public.access.tablesdbdesign)
  • Re: SQL-Server nach dBase
    ... The syntax that you are using is supported only by the Microsoft Jet OLE DB ... Jet Provider, the easiest solution would be to create an Access database ... SQL Server, then use SQL syntax over a SQLClient connection to SELECT INTO ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: syntax for calling Query with parameter from VB
    ... I am aware of the CREATE PROCEDURE syntax in JET. ... I noted that the O.P. was using DAO code which doesn't ... The SQL Server T-SQL equivalents ...
    (microsoft.public.access.modulesdaovba)
  • Re: Which comes first criteria or Join...
    ... Can't test on Pubs at the moment, but the following works, in Northwind: ... from Jet, returns nothing (since no author is known to live in Iqaluit, ... So, with MS SQL Server, really, the ON clause is evaluated first, then, ... was not possible with the ANSI-89 syntax. ...
    (microsoft.public.access.queries)