Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
declare
@inv table (item varchar(50), supplierid int)declare
@supplier table (supplierid int, weight int)set
nocount oninsert
into @inv values ('item1', 1)insert
into @inv values ('item1', 2)insert
into @inv values ('item2', 2)insert
into @inv values ('item2', 3)insert
into @supplier values(1, 30)insert
into @supplier values(2, 20)insert
into @supplier values(3, 10)-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3
select
item, ps2.supplierid from @supplier ps2 join(
select item, min(ps.weight)'weight'from
@inv inv join @supplier ps on inv.supplierid=ps.supplieridgroup
by item) iw on ps2.weight=iw.weight Is there a better alternative to thisThanks in advance,
Mike
Join to select a 'weighted' column
yoshistr
Thank you very much,
I will look at both.
Mike
Amelia
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @supplier as s
join @inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @supplier as s
join @inv as i
on i.supplierid = s.supplierid
group by i.item
) as si