selecting specific combination

Question

selecting specific combination

moadh.bs

SSC Veteran

Points: 299
More actions
April 22, 2009 at 11:05 am

#199729

Hi
I have a list of products
a product can be of type 1 or type 2
I have to find the right product of type 2 for every one in type 1
I get here all the possible match between type 1 and type 2 products (0 mean that they match)
the probleme is that every product must be retrived in only one row in the result
this is where I am
mv1 mv2 match
----------- ----------- -----------
1 2 0
5 2 0
7 2 0
1 6 0
5 6 0
7 6 0
1 8 0
5 8 0
7 8 0
10 3 0
11 4 0
10 9 0
and this is where I wanna go
mvt1 mvt2
----------- -----------
1 2
5 6
7 8
10 3
11 4
any T-sql suggestion please
thx

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Sorry but I don't understand your request. The "match" column contains only zeros. Why does 1 match with 2 but not with 6 and why does 7 match with 8 but not with 6?

Greets

Flo

moadh.bs SSC Veteran Points: 299 More actions · Answer 2

thanks Flo

but just forget about the match column,

if we take the 3 lines where mv1 = 1 we will get

1 with 2,

1 with 6,

1 with 8

and because the first couple we get is 1 with 2 thats why we put it on the result

now the next product mv1=5 here the first couple is 5 with 2 but the product number 2 already exist in the result (2 is affected) so we will insert the next couple 5 with 6

so in the end we shouldn't find any duplicated product in the results

I hope this is clearer

I thought about a insert or update querie with a left join on the destination table to avoid adding already existing products but unfortunately it doesn't work

this is my test table

create table #coupleProduct

(

p1_id int,

p2_id int

)

insert into #coupleProduct (p1_id,p2_id)

select 1 , 2 UNION ALL

select 5 , 2 UNION ALL

select 7 , 2 UNION ALL

select 1 , 6 UNION ALL

select 5 , 6 UNION ALL

select 7 , 6 UNION ALL

select 1 , 8 UNION ALL

select 5 , 8 UNION ALL

select 7 , 8 UNION ALL

select 10 , 3 UNION ALL

select 11 , 4 UNION ALL

select 10 , 9

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Try this

WITH CTE AS (

SELECT p1_id,p2_id,

ROW_NUMBER() OVER(PARTITION BY p1_id ORDER BY p2_id) AS rn1,

ROW_NUMBER() OVER(PARTITION BY p2_id ORDER BY p1_id) AS rn2

FROM #coupleProduct)

SELECT p1_id,p2_id

FROM CTE

WHERE rn1=rn2

ORDER BY p1_id

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

moadh.bs SSC Veteran Points: 299 More actions · Answer 4

thanks a lot, this is what i was looking for 😀

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Florian Reischl

SSC-Dedicated

Points: 37301

April 23, 2009 at 3:32 am

#982727

Great solution Mark! 🙂