Re: Q re Subqueries (Join Predicate) in Access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Simon.

I wouldn't use the []. syntax because it is specific to old versions
of Access, underdocumented, and causes problems in current versions
of Access.

The other example was just an example, I didn't try to work out
the details.

As I understand your question, you wanted to have a union of
table1 left join table2 where (something table2)
with
table1 left join table2 where (nothing table2)

The first query alone doesn't work, because when you put the
criteria on table2, you exclude the table 1 records with no table2.
So you get the non-matching records from the union with the
second query.

You can do that with a Stored Procedure (a query on table2),
or a Correlated Subquery (using [].) or a Union Query, or an
OR Clause in your criteria.

You can't use just a simple OR ..Null clause, because that doesn't
work, because WHERE criteria are evaluated before the join, and
the null doesn't exist in the base table, only in the joined recordset.

So in my example I used a NOT EXISTS correlated subquery
as the criteria for the Table1 records not matched in table2

select ... from table1, table2 where
something table2
or
nothing table2

Anything you can do with a join, you can do with a WHERE
clause. In fact, the old ANSI SQL didn't have left and right joins:
Access was one of the first popular database systems to use
LEFT JOIN and RIGHT JOIN, and it really irritated ORACLE
users, who were always complaining 'Access doesn't use ANSI
SQL' .

The EXISTS clause is the key to doing complicated selections
that you can't figure any other way of doing. In the example,
matching records in T1 and T2, are returned by the first half
of the criteria. The FALSE = EXISTS (normally written NOT
EXISTS) clause includes all the other records, like the second
half of the union query.



"Simon Woods" <simonjwoods@xxxxxxxxxxx> wrote in message
news:uWnglRLFJHA.4628@xxxxxxxxxxxxxxxxxxxxxxx
david wrote:
SELECT t1.Key, t1.Col1, t1.Col2, t1.Col3, Count(e1.Key), Max(e1.Col1)
FROM Table1 t1
LEFT JOIN
[
SELECT Key, Col1
FROM Table2
WHERE Col2 =3
]. as e1
ON t1.Key = e1.Key
GROUP BY t1.Key,t1.Col1, t1.Col2, t1.Col3

Note the full stop following the closing square bracket. Queries like
this
should not be saved or opened in design view in Access 2K+, but may
be written to a DAO querydef. Avoid the use of square brackets around
field names, as the combination confused some versions of the A2K parser.

I wouldn't do that: I'd use something like this:

SELECT t1.Key, t1.Col1, t1.Col2, t1.Col3, Count(e1.Key), Max(e1.Col1)
FROM Table1 as t1 left join table1 as t1A
where (
(t1.key = (
select key from table2
where (table2.col2 = 3)
)
)
OR (
FALSE = EXISTS (select table2.key from table2
where (table2.key = t1.key)
and (table2.col2 = 3)
)
)
)

GROUP BY t1.Key,t1.Col1, t1.Col2, t1.Col3


(david)

Thanks David ... though (and I hope you don't mind my saying) I,
personally, don't find it as readily understandable.

Perhaps I'm misunderstanding ...

how are you getting e1.Key and e1.Col1 from any underlying table?
Why left join table1 to itself?
On what are you joining table1 to itself?
Could you explain it a bit more?
Why you would do it this way? Is it for performance?

Thx

S


.

Relevant Pages

  • how to get unique resultset
    ... Lets say I join them on MainID... ... from table1 but my criteria will be checking values in table2. ... I can join the tables and check my criteria in the WHERE clause (say, ...
    (microsoft.public.access.queries)
  • Re: Copy entire column from one table to another (sql)
    ... appears more than one time in the FROM clause, ... CONSTRAINT PRIMARY KEY CLUSTERED ... Insert Into Table1 Values ... Insert Into Table2 Select * From Table1 ...
    (microsoft.public.vb.database.ado)
  • problem agian with outer joins, pls. Help
    ... But after entering some records to the table2, this query ... The table1 has only one field with values from ... in table2 that fits your criteria then only one record ...
    (microsoft.public.access.queries)
  • Re: Display Changed Records
    ... that last clause should have been: ... -- Also use table1 values to report rows deleted from table2 ... UNION ALL ...
    (microsoft.public.sqlserver.programming)
  • Re: Oracle sql completed with warnings...
    ... from table1 a, table2 b ... One aggregated column ... ... No GROUP BY clause ...
    (comp.databases.oracle.server)