Sorry,
I should have laid it out better.
The "B.A_ID=NULL" is part of the outer join - as in:
"A A2 RIGHT OUTER JOIN B ON B.A_ID = A2.ID"
Because it's a handed join it will let the nulls through on the RH table
"B" so I should have written:
"A A2 LEFT OUTER JOIN B ON B.A_ID = A2.ID"
My concern with the NULL was whether the condition would be applied
correctly, by Interbase, if A2.ID is not part of the resultset - I'll test
it later (after dinner).
On the BDE with null thing: Some of the "null" statement don't work on
LocalSQL as you'd expect, but interestingly I've found that the AsXXX
equivalent does - eg null string can be tested for as empty, integer as 0
etc. Not that I would want to rely on this in a production environment or
across versions.
Max
[Terry Said]
This won't quite work - B.A_ID=A2.ID where A2.ID IS NULL will (I think)
never be true. The SQL rules are that comparisons with either field null
return null. I think what you need to do is ON ((B.A_ID=NULL) and
(A2.ID=NULL)).
I've had all sorts of problems with using nulls with the BDE... even things
that work fine on small databases but produces invalid arguments (of all
things) on large databases. I'm not using the latest BDE though.
Terry
>Also if the list of values returned by the subquery is large try doing it
the other way - then hopefully the subquery takes less time to run on each
iteration:
delete from A where ID in
(select B.A_ID from A A2 RIGHT OUTER JOIN B
ON B.A_ID=A2.ID where A2.ID IS NULL)
the syntax is "off the top of my head" - (handing should be right? - "where
A2.ID IS NULL" may be dead-in-the-water).
Max
<
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
