David said:
Of course, maybe we're talking past each other -- I'm talking about
running the SQL directly, in the SQL Server tools, rather than
running it in Access via ODBC (which takes care of any
incompatibilities).
No, I'm talking about importing the Access tables into SQL Server, and
pasting the SQL that Access generated into SQL Query Analyzer.
If you want your SQL to be upsizable to SQL Server with the least
trouble, the best thing to do is not to muck around with implicit
vs. explicit joins, but to switch Access to use ANSI 92 SQL.
I've never touched that option
Sure, they're easy enough to find in BOL ... let's see ... ah, here we go -
it's long so I'm putting it at the bottom
Sorry, but I don't know what you're referring to with "*=". I don't
recognize that as valid Jet SQL.
It isn't - I should have said "... the *= syntax for performing outer joins
implicitly in the
WHERE clause in SQL Server ..."
AFAIK one cannot perform an outer join in Access without using the explicit
JOIN syntax.
****paste from SQL BOL***********************************************
The rows selected by a query are filtered first by the FROM clause join
conditions, then the WHERE clause search conditions, and then the HAVING
clause search conditions. Inner joins can be specified in either the FROM or
WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE
clause search conditions, depending on whether the join conditions are in
the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL
outer joins in the WHERE clause is not recommended, is no longer documented,
and will be dropped in a future release.
For example, these queries both specify a left outer join to SELECT 23 rows
that display the title identification number, title name, and the number of
books sold:
-- Join in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
-- Join in FROM clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
In this query, a search condition is also specified in the WHERE clause:
-- Join and search condition in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
AND s.stor_id = '7066'
The condition stor_id = '7066' is evaluated along with the join. The join
only selects the rows for stor_id 7066 from the sales table, but because it
is an outer join null values are supplied as the store information in all
the other rows. This query returns 18 rows.
The join condition can be moved to the FROM clause, and the stor_id
condition left in the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
WHERE s.stor_id = '7066'
This query returns only two rows because the restriction of stor_id = '7066'
is applied after the left outer join has been performed. This eliminates all
the rows from the outer join that have NULL for their stor_id. To return the
same information with the join condition in the FROM clause, specify the
stor_id = '7066' condition as part of the ON join_criteria section in the
FROM clause and remove the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
AND s.stor_id = '7066'
