"Joe Martinez" <joe@...> wrote in message
news:3C4480FE.12985166@......

>
> 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
>
> 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.

It won't work. If vendor V1, V2 and V3 can provide the same product, then
even if you can write your "if there's a match", then anyone else will be
picked when there's no preferred vendor V1. If V2 and V3 are found, both
will be summed and you will get totally wrong (and higher) prices.

> 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"?

The join mechanism is internal to the implementation of the engine. Anyway,
I don't understand what strange thing are you asking for.

> Second, is it possible to order by a boolean
> expression?

No, but people use 0 and 1 to generate an extra field in UNION ALL
constructions and so order finally by that field. However, the GROUP clause
applies only to each branch in the UNION ALL.

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

Enjoy writing obscure SQL statements that carry bad performance as a bonus,
then...

This thing is simple to solve with a selectable stored procedure that's
almost undistinguishable from a table in a SELECT statement. Here's an
statement made with only one table, but that you can replace and augment it
with your tables:

select t.rdb$relation_id from rdb$database t
join rdb$database u on t.rdb$relation_id =
(select first 1 v.rdb$relation_id from rdb$database v
where v.rdb$relation_id = u.rdb$relation_id)
group by t.rdb$relation_id

=>
select L.dept,
sum(L.itemqty * V.vpcost) as COST
from lineitems L
join vendorproducts V on
V.vppartcode=L.prodpartcode
and
(V.vpvendor=L.prodsource
or v.vpvendor=
(select first(1) V2.vpvendor from vendorproducts V2
where V2.vppartcode = L.prodpartcode
and V2.vpvendor<>L.prodsource)
and not singular(select * from vendorproducts V3
where V3.vppartcode = L.prodpartcode
and V3.vpvendor=L.prodsource)
)
group by L.dept

You can test it by commenting the sum token (not the whole line) and the
group line.

Since this is a correlated query, it will be really slow anyway. Probably I
should write a book named "SQL for masochist developers" and I would become
rich.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing