[Info-Ingres] The little row that wasn't there

[Karl & Betty Schendel]

At 4:36 PM -0400 10/2/07, Mike Ossing wrote:
>Hello,
>
>We have sales data in SalesTable11 and SalesTable12. What we'd like to report on are the rows in SalesTable11 that aren't in SalesTable12.
>
>If Invoice 1234 is in both SalesTable11 and SalesTable12, we an skip that. But, if Invoice 1235 is in SalesTable11, but not in SalesTable12, we want the selected fields on that invoice to appear on our report.
>
>We've tried several SQL statements, and we're close, but we're still getting to much of the data from ST11 and ST12 in our reports.

select ... from st11
where not exists (select * from st12 where st11.invoice_no = st12.invoice_no)
and (other conditions on st11 here if any)

If there's more than one condition needed to match up invoices, just
list 'em all out inside the not exists subselect.

NOT IN instead of not exists works fine, but watch out for nullable
columns -- if the not-in column is nullable you'll end up with an
SE-join (translation:  likely to be dead slow).

If you're using an Ingres older than 2.5 you might want to write
out the equivalent outer join instead:

select ... from st11 left join st12 on st11.invoice_no = st12.invoice_no
where st12.tid is null

If you're running 2.5 or later, let Ingres do the rewrite.
If you get no rows with the explicit outer join, use some other
non-nullable st12 column instead of tid (old bug workaround).

Karl