Full outer join using each line after

Question

I am wondering if anyone will find a clear solution to this problem. I am trying to select data from multiple tables if the records match line by line. I am basically after a complete external connection, but there is one important difference. If I have four rows with a specific value in a column in which I am included in one table and three rows with this value in another, I want the first three results to be merged and the fourth as if there was no match .

The reason for this is the creation of a reconciliation report that ensures that transactions are not counted several times when comparing results. I can get around this problem by using a little grouping and some aggregate functions, but it hides some details that I would like to keep.

The following is an example to show what I'm doing after, with an invalid / pseudo-code in the comments, illustrating how I think of it as work:

declare @t1 table (id bigint identity(1,1) primary key clustered, foreignKeyId bigint, otherData nvarchar(10)) declare @t2 table (id bigint identity(1,1) primary key clustered, foreignKeyId bigint, moreData nvarchar(10)) insert @t1 select 1, '1.1.1' union all select 1, '1.1.2' union all select 1, '1.1.3' union all select 3, '1.3.1' union all select 3, '1.3.2' union all select 3, '1.3.3' union all select 4, '1.4.3' insert @t2 select 1, '2.1.1' union all select 1, '2.1.2' union all select 1, '2.1.3' union all select 2, '2.2.1' union all select 3, '2.3.1' union all select 3, '2.3.2' union all select 5, '2.5.1' union all select 5, '2.5.2' --demo of the functionality i'm hoping to acheive -- /* select t1.id id1 , t2.id id2 , t1.foreignKeyId fk1 , t2.foreignKeyId fk2 , t1.otherData otherData , t2.moreData moreData from @t1 t1 full funky join @t2 t2 on t1.foreignKeyId = t2.foreignKeyId order by t1.id, t2.id --we'd need an order by to ensure the match could be applied in a predictable manner */ -- declare @funkyjoin table (id1 bigint, id2 bigint, fk1 bigint, fk2 bigint, otherData nvarchar(10), moreData nvarchar(10)) declare @id1 bigint, @id2 bigint insert @funkyjoin (id1, fk1, otherData) select id, foreignKeyId, otherData from @t1 while exists(select 1 from @t2) begin select top 1 @id2 = id from @t2 order by id set @id1 = null select top 1 @id1 = id1 from @funkyjoin where fk2 is null and fk1 in (select foreignKeyId from @t2 where id = @id2) if @id1 is null begin insert @funkyjoin (id2, fk2, moreData) select id, foreignKeyId, moreData from @t2 where id = @id2 end else begin update @funkyjoin set id2 = @id2 , fk2 = fk1 --since we're joining on this we can just match it , moreData = (select moreData from @t2 where id = @id2) where id1 = @id1 end delete from @t2 where id = @id2 --since this is only an example let not worry about keeping our source data end select * from @funkyjoin order by coalesce(id1, id2) 

I wrote a similar solution when this script happens earlier on spreadsheets: http://officemacros.codeplex.com/#WorksheetMergeMacro

Answer 1

If I understand correctly, this may be what you need:

 select * from ( select *, row_number() over (partition by foreignKeyId order by id) as n from @t1 ) t1 full outer join ( select *, row_number() over (partition by foreignKeyId order by id) as n from @t2 ) t2 on t1.foreignKeyId = t2.foreignKeyId and t1.n = t2.n 

Answer 2

The best way to use strings is to add the pseudo-row number (using ROW_NUMBER) and include this in the connection.