SQLTeam.com | Weblogs | Forums

Join two tables using two criterias

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.

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]

1 Like

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.

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

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.

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

1 Like