Subject Re: [IBO] IBO Doesn't like this query
Author Svein Erling Tysvaer
At 00:18 22.08.2002 -0700, you wrote:
>I want to find all of the records in the PRODUCTS table that either:
>A) Don't match any records in the VENDORPRODUCTS table
>B) If they DO match a record in VENDORPRODUCTS, the VPCOST field in
>VENDORPRODUCTS is either 0 or NULL.
>In these cases, the "match" criteria is that PRODUCTS.PRODBARCODE =
>Basically, I'm trying to find all PRODUCTS that don't link to a nonzero cost.
>Is there a better way to do this?

select * from products p
where not exists(select 1 from vendorproducts v where p.prodbarcode=v.vpupc
and p.prodsource=v.vpvendor and v.vpcost > 0)

This is assuming that you do not want to list those entries for which there
exists both an empty vpcost and one vpcost which is greater than 0, and
that vpcost is never negative.