--- In firebird-support@yahoogroups.com, Magnus Titho
<magnus.titho@k...> wrote:

Thanks for your answer, Magnus.

I should from time to time slap my head for better concentration (or
just make a break more often during work...).
The reason why I couldn't get the syntax for the left join correct was
that I was mixing CrystalReports-syntax (isnull) with
Firebird-syntax(is null)...

Your example works for me, thanks.
And it is indeed running a bit faster than the "count"-version.
Actually, this is only 12 miliseconds (917 to 929) in average after
running dozens of tests with CR-monitor, but I also have a slight
detesting of the "count"-method, so I will be favouring the "left
outer join".

Thanks again for your time,
André

> lysander_fb wrote:
>
> > I was trying a left outer join of both tables, and filtering for NULL
> > values on the right (bills) side. But I did not manage to get the
> > syntax correct.
>
> select t1.orderno, t1.ordertitle
> from orders t1
> left join bills t2 on t2.bill_reference = t1.orderno
> where t2.bill_id is null
>
> should work and is probably faster than your approach (afaik "count" is
> a performance-killer), but you should test that yourself.
>
> > Thanks for ideas,
> > André
>
> Magnus