since City is a field from authors, not from books,

(select authorid from books where City = 'Iqaluit')


is not making sense. It would if the condition was implying the unpreserved
table, but it is not the case, here.


Vanderghast, Access MVP


"Bob Barrows" wrote in message
...
David W. Fenton wrote:
"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

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'

I wouldn't call this a JOIN.

Why not? it's equivalent to this, which is certailnly a join:

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




It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

See Michel's post. It doesn't.

--
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"