How to find unique combination from join in sql server -


i have 2 tables t1 , t2 below, have 2 tables below,

id	d 	v	a  1	d1	v1	-70  2	d1	v2	70  3	d1	v3	-70  4	d2	v1	120    id	d 	v	a  1	d1	v11	-70  2	d1	v12	70  3	d1	v13	-70  4	d2	v31	-120

id	d 	v	a  1	d1	v11	-70  2	d1	v12	70  3	d1	v13	-70  4	d2	v31	-120

i need find out unique combinations of records who's amounts results in zero. following expected result set.

d	v  d1	v2  d1	v11  d1	v1  d1	v12  d2	v1  d2	v31

d v d1 v2 d1 v11 d1 v1 d1 v12 d2 v1 d2 v31

i have tried inner join on column d , amount using row_number() function not satisfy requirement. gives first unique combination.

,temp1 (select a.v v1 ,b.v v2,a.d d1 ,b.d d2,a.a actual ,b.a acrual              t1  inner join t2 b on a.d =b.d             a.actual +b.acrual = 0)  select *  tbl inner join           (select v1,v2,d1,d2, actual,acrual, row_number() over(partition v1 order v1) r1 ,row_number() over(partition v2 order v2) r2            temp1)f on (a.v = f.v1 or a.v = f.v2) , a.d = f.d1   r1 = 1 , r2 = 1 

if remove condition r1=1 , r2=1 condition , put r1=r2 wrong combination selected ( v2- v13 combination selected).

updated answer: confused example result in fiddle. said wanted unique pairs, why below appear twice?:

insert result (dossier,voucher) values('d1','y100022') insert result (dossier,voucher) values('d1','y100022') 

anyway assuming want given row in table part of 1 pair e.g.
should returned query:
d1 x10002
d1 y100022
should not returned query:
d1 x10002
d1 y100033

use below query:

with uniquepairs( aid, bid, ar, br ) (     select a.id, b.id,         row_number() over(partition a.dossier, a.voucher order a.dossier, a.voucher) ar,         row_number() over(partition b.dossier, b.voucher order b.dossier, b.voucher) br     t1         inner join t2 b on a.dossier = b.dossier , a.amount+ b.amount = 0 ) select a.dossier, a.voucher uniquepairs     inner join t1 on aid = a.id ar = 1 , br = 1 union select b.dossier, b.voucher uniquepairs     inner join t2 b on bid = b.id ar = 1 , br = 1 

Comments

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -