Here's a skeleton of my current query:

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

Baiscally, this query totals up a total cost for the items sold by
department. It looks up the cost by joining the vendorproducts table,
which contains the cost. The first join condidtion is that the partcode
numbers match. The second join condition tells it to use the
vendorproducts entry where the vendor matches the prodsource field (this
is the preferred vendor).

Here's the problem. The second join condition is too restrictive. If
there's no entry in vendorproducts that matches prodsource, then it
returns a null. But, if I take that condition out, then it simply
ignores the preferred vendor (prodsource).

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.

Does anyone know of a way to make it do that?

One idea that I had was to make it return two separate columns, looking
it up differently each time. To do that, however, would require two
separate joins to the same table within one query. Is that possible?
If so, how?

My other idea was to somehow specify an order in which the join should
happen. They would require 2 things. First, is it possible to specify
an "order by" ONLY for the join results, and have it join to the first
row in the "order by"? Second, is it possible to order by a boolean
expression?

Any help would be greatly appreciated.

P.S. I want to AVOID using a stored procedure.

Thanks,
Joe