Subject Re: Why would left outer join return FEWER records?
Author Adam
--- In, "Joe Martinez" <joe@...>
> Oops, nevermind. I figured out the reason. The VENDORPRODUCTS
> has a STOREID field as well, and so I guess the unqualified STOREID
> the the "where" clause was causing it to throw out completely the
> where the "joined" STOREID from VENDORPRODUCTS was null. I changed
> the "where" clause to say A.storeid, and that fixed it.
> I thought I was losing my mind for a minute!

That surprises me. Normally Firebird (1.5 dialect 3) is pretty strict
on syntax checking and would tell you something like:

SQL error code = -204

Ambiguous field name between table VENDORPRODUCTS and table PRODUCTS



Are you using an older version or different dialect?

You have just made a very good case in favour of this
strictness.Count yourself lucky that you discovered the error now
rather than later.