On Tue, 15 Jan 2002 15:20:54 +0000 (UTC), joe@... (Joe
Martinez) wrote:

>
>select lineitems.dept, sum(lineitems.itemqty * vendorproducts.vpcost) as
>COST
>from lineitems
>left outer join vendorproducts on
>vendorproducts.vppartcode=lineitems.prodpartcode
>and vendorproducts.vpvendor=lineitems.prodsource
>group by lineitems.dept
> [...]
>What I want is for it to match the preferred vendor IF THERE IS A
>MATCH. Otherwise, it can use any one that it finds.
>

Try something like:

select ...
from lineitems LI inner join vendorproducts VP
on LI.prodpartcode = VP.vppartcode
where (LI.prodsource = VP.vpvendor)
or ((LI.prodsource is null)
and (VP.vpvendor = (select max(vpvendor) from vendorproducts
where vppartcode = LI.prodpartcode)))
group by ...

Note 1) I replaced your left join by an inner join. If you really
need the query to return departments having a null sum you have to
tweak it a bit.

Note 2) You said "any", so there is no special reason for using max.
You can use min, if you want.

>
>Thanks,
> Joe

Kind regards
Carl van Tast