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 said:
david said:
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
