Join two tables using two criterias

Transact-SQL
,
1

I'm trying to combine two tables using two criterias

  1. if table 1 referance & amount matched with table 2 referaance & amount join those records
  2. for the items that were not matched above criteria join based on the table 1 amount match with table 2 amount.

My tables are as follows.

CREATE TABLE [TEST].[dbo].[tb1]
(date DATE , ref INT, amount INT )

INSERT INTO [TEST].[dbo].[tb1] VALUES
('2017-01-01',1000,500),
('2017-01-15',null,500),
('2017-02-01',2000,1000),
('2017-02-15',null,1000)

CREATE TABLE [TEST].[dbo].[tb2]
(date2 DATE , ref2 INT, amount2 INT )

INSERT INTO [TEST].[dbo].[tb2] VALUES
('2017-01-04',1000,500),
('2017-01-20',null,500),
('2017-02-05',2000,1000),
('2017-02-25',null,1000)

How can i achive this

Date Ref Amount Date Ref Amount
1-Jan-17 1000 500 4-Jan-17 1000 500
5-Jan-17 500 10-Jan-17 500
1-Feb-17 2000 1000 5-Feb-17 2000 1000
15-Feb-17 1000 25-Feb-17 1000

Code i wrote so;
Select *

from [TEST].[dbo].[tb1] tb1
join [TEST].[dbo].[tb2] tb2
ON (tb1.amount = tb2.amount2 and tb1.ref = tb2.ref2)

union all

Select *

from [TEST].[dbo].[tb1] tb1
join [TEST].[dbo].[tb2] tb2
ON (tb1.amount = tb2.amount2)

Highly appreciate your help on this.

2

Here you are:-

CREATE TABLE #tb1 (date DATE , ref INT, amount INT )

INSERT INTO #tb1 VALUES
('2017-01-01',1000,500),
('2017-01-15',null,500),
('2017-02-01',2000,1000),
('2017-02-15',null,1000)

CREATE TABLE #tb2 (date2 DATE , ref2 INT, amount2 INT )

INSERT INTO #tb2 VALUES
('2017-01-04',1000,500),
('2017-01-20',null,500),
('2017-02-05',2000,1000),
('2017-02-25',null,1000)

select * from (
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2 and a.ref = b.ref2
where a.ref is not null and b.ref2 is not null
union all
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2
where a.ref is null and b.ref2 is null
)main
order by [date]

3

1 Like
4

Thanks for the answer, it works prefectly.

I have another problem as some times i have to match previously unmatched records( in first two slelct statements) that are having similar amounts but it may or may not have reference numbers.

-- Insert new data
CREATE TABLE #tb1 (date DATE , ref INT, amount INT )

INSERT INTO #tb1 VALUES
('2017-01-01',1000,500),
('2017-01-15',null,500),
('2017-02-01',2000,1000),
('2017-02-15',null,1000),
('2017-03-01',3000,500)

CREATE TABLE #tb2 (date2 DATE , ref2 INT, amount2 INT )

INSERT INTO #tb2 VALUES
('2017-01-04',1000,500),
('2017-01-20',null,500),
('2017-02-05',2000,1000),
('2017-02-25',null,1000),
('2017-03-05',null,500)

In this case i think i can add a another select statement with union, however i want to exclude the already matched items in first two selet statements.

-- My code

select * from (
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2 and a.ref = b.ref2
where a.ref is not null and b.ref2 is not null
union all
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2
where a.ref is null and b.ref2 is null
union all
select *
from #tb1 a
left join #tb2 b on (a.amount = b.amount2 and a.ref != b.ref2)
where b.amount2 is not null
)main
order by [date]

-- My expected result is something like this ( above statement is not giving below result)


date ref amount date2 ref2 amount2
01-01-17 1000 500 04-01-17 1000 500
15-01-17 NULL 500 20-01-17 NULL 500
01-02-17 2000 1000 05-02-17 2000 1000
15-02-17 NULL 1000 25-02-17 NULL 1000
01-03-17 3000 500 05-03-17 NULL 500

How can i achive this, highly appreciate your help on this.

5

CREATE TABLE #tb1 (date DATE , ref INT, amount INT )

INSERT INTO #tb1 VALUES
('2017-01-01',1000,500),
('2017-01-15',null,500),
('2017-02-01',2000,1000),
('2017-02-15',null,1000),
('2017-03-01',3000,500)

CREATE TABLE #tb2 (date2 DATE , ref2 INT, amount2 INT )

INSERT INTO #tb2 VALUES
('2017-01-04',1000,500),
('2017-01-20',null,500),
('2017-02-05',2000,1000),
('2017-02-25',null,1000),
('2017-03-05',null,500)

select
a.[date]
,a.ref
,a.amount
,b.date2
,b.ref2
,b.amount2
from (select ROW_NUMBER()over(order by (Select 0))rn,* from #tb1)a
Left join (select ROW_NUMBER()over(order by (Select 0))rn,* from #tb2) b on a.rn=b.rn

6

7

Thanks for the reply,

However above code just match the tb1 each row number with the respective row number of tb2, without checking criterias such as amount, referance no. etc.

8

Try this:-

CREATE TABLE #tb1 (date DATE , ref INT, amount INT )

INSERT INTO #tb1 VALUES
('2017-01-01',1000,500),
('2017-01-15',null,500),
('2017-02-01',2000,1000),
('2017-02-15',null,1000),
('2017-03-01',3000,500)

CREATE TABLE #tb2 (date2 DATE , ref2 INT, amount2 INT )

INSERT INTO #tb2 VALUES
('2017-01-04',1000,500),
('2017-01-20',null,500),
('2017-02-05',2000,1000),
('2017-02-25',null,1000),
('2017-03-05',null,500)

select
[date]
,[ref]
,[amount]
,[date2]
,[ref2]
,[amount2]
from (
select distinct
ROW_NUMBER() over (partition by [date] order by [date])rn, *
from (
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2 and a.ref = b.ref2
where a.ref is not null and b.ref2 is not null
union
select *
from #tb1 a
left join #tb2 b on a.amount = b.amount2
where a.ref is null and b.ref2 is null
union
select *
from #tb1 a
left join #tb2 b on (a.amount = b.amount2 or a.ref != b.ref2)
where b.amount2 is not null

	)main	
	where date2 > date

)main2 where rn = 1

1 Like
9

1 Like