--- In firebird-support@yahoogroups.com, "Gary Benade" wrote:

> This query should return all the bulk rows joined with
> backstocklevels for a certain date if any exist.
>
> select *
> from
> bulk b
> left outer join backstocklevels bs
> on (b.link = bs.itemlink and bs.zdate = '01-dec-2004')
>
> correctly returns 11 rows
>
> select *
> from
> bulk b
> left outer join backstocklevels bs on (b.link = bs.itemlink)
> where bs.zdate = '01-dec-2004'
>
> returns no rows
>
> I would normally have used the second query without a second thought
> but the client pointed out that the reports don't add up, hence the
> first query. Is their another way of coding this that is
> stylistically correct and wont confuse the optimiser and 3rd party
> tools. I have a sneaky feeling I am missing something blindingly
> obvious here....

If you really want an outer join, then your first query is correct.
The only place to limit which rows of the right table get included in
the result without affecting what is returned from the left table is
in the join itself. As Arno said, it may be useful to use the right
table in the where clause if you're checking for records in the left
table that doesn't exist in the right table, i.e.

select *
from
bulk b
left outer join backstocklevels bs on (b.link = bs.itemlink)
where bs.zdate is null

but other than that, I've yet to see a case where adding the right
table to the where clause isn't equivalent to an inner join.

If you still feel uncomfortable, you may have another option if there
only exists 0 or 1 row in the right table that links to each record in
the left table. If so, using a subselect may be an alternative:

select *, (select bs.field from backstocklevels bs
where b.link = bs.itemlink and bs.zdate = '01-dec-2004')
from
bulk b

Though this is more cumbersome if you want more than one field from
backstocklevels.

HTH,
Set