Match two tables using multiple criterias

Transact-SQL
,
1

I have two tables, which i need to match the records.
Table1 fields are client, ref1, ref2, amount
Table 2 fields are client,ref2,amount

  1. Eventhough ref2 field available in both tables in table 1 it is not available in some cases.
    So i have to look for matches based on client & amount for those items that does not have ref2.
  2. Also in table 1 records are in more detailed level. So the amounts has to sum up to each ref1 level under each client before match with table 2.
    but i want to first look for ref2 & value matches & then matche the remain items solely on values.

I'm not sure how code for this.It is great If someone can help on this.
--Table1
CREATE TABLE [TEST].[dbo].[tb1]
(Client FLOAT, Ref1 FLOAT, Ref2 FLOAT, Value FLOAT)

INSERT INTO [TEST].[dbo].[tb1] VALUES
(100,1001,null,2000),
(100,1001,null,2000),
(100,1002,20001,5000),
(200,2001,null,1500),
(200,2001,null,2000),
(200,2002,null,4000),
(300,3001,10001,4000)

CREATE TABLE [TEST].[dbo].[tb2]
(Ref nvarchar(50), Client FLOAT, Ref2 FLOAT, Value FLOAT)

INSERT INTO [TEST].[dbo].[tb2] VALUES
('A',100,20000,4000),
('B',100,20001,4000),
('C',200,10001,3500),
('D',200,10002,4000),
('E',300,null,4000)

--My code i tries is below. but it does not check the ref2 column at all. This methode is taking a long time as my real tables are big. Hope someone can tell me a better way.

SELECT
distinct Set2.Ref1,
Set1.Client,
Set1.TotalValue,
Set2.ref2,
Set2.Value,
set3.ref 't2 ref',
Set3.Client 't2 client',
Set3.Ref2 't2 ref2',
Set3.Value 't2 value'
FROM

(SELECT t1.Client, t1.ref1, Sum(t1.Value) as TotalValue
FROM [TEST].[dbo].[tb1] t1
GROUP BY t1.Client, t1.ref1
) as Set1

INNER JOIN

(SELECT t11.Client, t11.ref1, t11.ref2, t11.Value
FROM [TEST].[dbo].[tb1] t11
) as Set2
ON Set2.ref1 = Set1.ref1

INNER JOIN

(SELECT t2.Ref,t2.Client, t2.ref2, t2.Value
FROM [TEST].[dbo].[tb2] t2
) as Set3
ON Set1.TotalValue = Set3.Value
WHERE Set1.Client = Set3.Client and
Set1.Client = Set2.Client

My Result i'm getting

Ref1 Client TotalValue ref2 Value t2 ref t2 client t2 ref2 t2 value
1001 100 4000 NULL 2000 A 100 20000 4000
1001 100 4000 NULL 2000 B 100 20001 4000
1002 100 4000 20001 4000 A 100 20000 4000
1002 100 4000 20001 4000 B 100 20001 4000
2001 200 3500 NULL 1500 C 200 10001 3500
2001 200 3500 NULL 2000 C 200 10001 3500
2002 200 4000 NULL 4000 D 200 10002 4000
3001 300 4000 10001 4000 E 300 NULL 4000

2

I couldn't understand the question properly but this may be the solution that you are looking for ::

Select client
,Ref2
,Sum(value) As value
from tb1
where Ref2 is NULL
group by Client , ref1,ref2
union all
Select client
,Ref2
,value
from tb2
where Ref2 is NULL

3

Thanks for your reply. There were some mistakes in my question. Just now i rectified it.

I need to see the maching items in table 1 & table 2.

Unforthunatly i don't have a proper field to use for maching.
.
Tb1 ref2 & Tb2 ref2 column can match but both columns are not fully updated. However if there are matches based on ref2, client & amount, that we can consider as primary match.

If there are no maches based on ref2, client & amount, then it has to match using client & amount fields for the rows which does not contain a value in ref2.

In my code i match only client fields & amount fields. So i'm getting several invalid matches.
I hope there is a way to do this.
Thank you very much for your consideration.

4

I hope this works for you.

Select tb1.client
,tb1.Ref2
,sum(tb1.value)
from tb1
left join
tb2 on (tb1.client=tb2.client and tb1.ref2=tb2.ref2 and tb1.value=tb2.value)
group by tb1.client,tb1.ref2

5

Thanks for the reply, still I'm not getting the expected result.

Is there any way to pass a result of a condition like iif or case for the join method?