Joining Tables

Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.

Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678

I need to display the data in the following format.

PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004

But I'm getting the data replicated with all records joined every record.
Give the suggestion.Your data doesn't make sense in any way that would give you the query you want? How do you know which PId is a supplier or Customer? Without some kind of a key to indicate that, there's nothing you can do about your issue.|||Hai Madhavi,
Can you show us the query that you have written?

Madhivanan

Join to select a 'weighted' column

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 on

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

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
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

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
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

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike