At 09:23 AM 20-05-02 +0000, you wrote:

>Either I've got a problem with my database or with my understanding of outer
>joins.
>
>I expect the same number of rows from both of the queries below
>when there are no rows on the righthand side of the left outer join
>(b_matdet.b_md_worder_ref = b_order.b_o_ref fails)
>
>but the first one returns 1 row - fine and the second returns none.
>
>select * from b_matdet left outer join b_order on b_matdet.b_md_worder_ref =
>b_order.b_o_ref
>where b_matdet.b_md_ref = 7023
>
>select * from b_matdet left outer join b_order on b_matdet.b_md_worder_ref =
>b_order.b_o_ref
>inner join b_order_ops on b_order.b_o_ref = b_order_ops.b_o_ref
>where b_matdet.b_md_ref = 7023

No, your results are as expected. The left outer join says to return all
the "where-eligible" rows from the lefthand table, regardless of whether
there is a match with any rows on the righthand table; the inner join says
return those rows where both the join and the where criteria are met. If
you had specified any columns from the righthand table, they would have
been returned from the first query as nulls.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________