Re: Editing disabled by alternative to INNER JOIN

Hi, Chris.

SELECT * FROM teachers INNER JOIN subjects ON
teachers.name=subjects.taught_by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name=subjects.taught_by;

These two queries are _not_ equivalent in Jet (Access). The second query
uses a Cartesian Join, instead of an ANSI join. A Cartesian Join matches
every row in one table to every row in the other table (even when there is
no logical correlation). A query that uses a Cartesian Join is not
updateable, that's why you hear the beeps. The fact that you placed a WHERE
clause narrowed the results so that the results of the two queries look
identical. However, if you remove the WHERE clause in both queries and then
compare the results, you'll see two very different result sets. Only the
WHERE clause hides the effects of the Cartesian Join (except for the slower
speed when running the query).

I guess the INNER JOIN
construction makes possible some safety checking.

The INNER JOIN allows updateable recordsets, and is much more efficient in
Jet queries. It's easy to run out of resources when using a Cartesian Join
with larger data sets. They take a longer time for Jet to run than when
using an INNER JOIN, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Editing disabled by alternative to INNER JOIN
    ... These two queries are _not_ equivalent in Jet. ... uses a Cartesian Join, ... A query that uses a Cartesian Join is not ...
    (comp.databases.ms-access)
  • Re: Query is too complex after install Access 2007 SP 2 !?
    ... To be honest, the recurrent and inconsistant problems (sometimes they work, ... queries using a mix of UNION, Sub-Query and Outer Join is one of the main ... reasons that I stopped using JET for most of my work a few years ago. ... query problem would not be affected by decompiling. ...
    (microsoft.public.access.queries)
  • Re: Editing disabled by alternative to INNER JOIN
    ... These two queries are _not_ equivalent in Jet. ... In this case, the WHERE clause and JOIN is identical, so it ... There will be no Cartesian join in either case, ...
    (comp.databases.ms-access)