Comparing Rowwise

here's the problem, i used to do something in frontend now i'm trying to do it in backend

What i used to do is this.. Take sorted Table1 and Table2 data. Compare a Field say Table1.F1 with Table2.F1 if it matches i display it. Here's a example

TableA
100 QQQ
100 PPP
200 KKK
300 LLL
300 WWW
300 EEE

TableB
100 ZZZ
200 XXX
300 CCC
300 VVV
400 BBB

want output as.
100 QQQ 100 ZZZ
100 PPP <<NULL>>
200 KKK 200 XXX
300 LLL 300 CCC
300 WWW 300 VVV
300 EEE <<NULL>>
<<NULL>> 400 BBB

I thought it would be done by outer join, but i found any kind of join permutes all possibilities. Like for above 300 their would be 6records (3 * 2)

Since it's just one criteria i want to appy, i'm not able to define the query properly. So all i want is compare rows of two sorted table and display them accordingly. I'm not sure, if join is applicable here.

To add more, i'm also not able to apply ORDER BY in a subquery.

Please help

My first-pass approach would be to make derived tables of both tables that use the ROW_NUMBER function to generate sequence numbers. PARTITION your ROW_NUMBER based on your numeric field and ORDER your ROW_NUMBER based on the character field.

Once you have two derived tables that include sequence numbers (based on the ROW_NUMBER function) you should be able to FULL JOIN the two derived tables based on (1) the numeric field and (2) the sequence numbers.

Look up ROW_NUMBER and get familar with it; it is one of the more useful new features of SQL Server 2005.

***

Hmmm... The suborder is not stable. Must 'QQQ' match to 'ZZZ'? If so, I do not have enough information here to generate a stable ordering of this table.

Help?

|||

Maybe something like this?

declare @.tableA table
( rid integer identity primary key,
grouper integer,
someChars varchar(5)
)

declare @.tableB table
( rid integer identity primary key,
grouper integer,
someChars varchar(5)
)

insert into @.tableA values (100, 'QQQ')
insert into @.tableA values (100, 'PPP')
insert into @.tableA values (200, 'KKK')
insert into @.tableA values (300, 'LLL')
insert into @.tableA values (300, 'WWW')
insert into @.tableA values (300, 'EEE')

insert into @.tableB values (100, 'ZZZ')
insert into @.tableB values (200, 'XXX')
insert into @.tableB values (300, 'CCC')
insert into @.tableB values (300, 'VVV')
insert into @.tableB values (400, 'BBB')

select a.grouper,
a.someChars,
b.grouper,
b.someChars
from ( select grouper,
someChars,
row_number ()
over ( partition by grouper
order by rid
)
as Seq
from @.tableA
) a
full join
( select grouper,
someChars,
row_number ()
over ( partition by grouper
order by rid
)
as Seq
from @.tableB
) b
on a.grouper = b.grouper
and a.seq = b.seq

-- grouper someChars grouper someChars
-- -- --
-- 100 QQQ 100 ZZZ
-- 100 PPP NULL NULL
-- 200 KKK 200 XXX
-- 300 LLL 300 CCC
-- 300 WWW 300 VVV
-- 300 EEE NULL NULL
-- NULL NULL 400 BBB