Oops, nevermind. I figured out the reason. The VENDORPRODUCTS table
has a STOREID field as well, and so I guess the unqualified STOREID in
the the "where" clause was causing it to throw out completely the rows
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!

-Joe

--- In firebird-support@yahoogroups.com, Joe Martinez <joe@...> wrote:
>
> This has me totally confused.
>
> This query returns 34 rows:
>
> select
>
productskey,prodbarcode,prodprice,prodinventory,prodwarning,proddept,prodwanted,

>
prodsource,prodtax,storeid,prodaskqty,prodtax2,prodtax3,proddesc,proddiscountable,

>
prodlocation,prodaccmoney,prodaccvolume,prodnotes,prodserialized,proddateentered
> from products A
> where upper(proddesc) like "%BUTTON 41%" and storeid in (1) order by
proddesc
>
> This query returns only one row:
>
> select
>
productskey,prodbarcode,prodprice,prodinventory,prodwarning,proddept,prodwanted,

>
prodsource,prodtax,storeid,prodaskqty,prodtax2,prodtax3,proddesc,proddiscountable,

>
prodlocation,prodaccmoney,prodaccvolume,prodnotes,prodserialized,proddateentered,B.vpvendor
> from products A
> left outer join vendorproducts B on B.vpupc=A.prodbarcode and
> B.vpvendor=A.prodsource
> where upper(proddesc) like "%BUTTON 41%" and storeid in (1) order by
proddesc
>
> In the database, there are 34 records in PRODUCTS that meet the
> "where" condition, and only one record in VENDORPRODUCTS that meets
> the "join" condition.
>
> However this is behavior that I would expect from an INNER join, not
> a LEFT OUTER join. Shouldn't both queries return 34 rows???
>
> I am running this query in the Cursor tab of IB_SQL.
>
> -Joe
>