create table #a ( a int );
create table #b ( b int );
-- This table contains all combinations of the values from #a & #b
create table #ab ( a int , b int );
go
insert #a values( 1 );
insert #a values( 2 );
select * from #a;
/*
a
-----------
1
2
*/
insert #b values( 5 );
insert #b values( 6 );
select * from #b;
/*
b
-----------
5
6
*/
insert #ab values( 1 , 5 );
insert #ab values( 2 , 6 );
select * from #ab;
/*
a b
----------- -----------
1 5
2 6
*/
go
-- Objective: Get all combinations of rows from #a & #b irrespective of
-- whether there is one existing in the combinations table.
-- Solution : This one uses one of the least known features of the ANSI joins
-- i.e., using parenthesis to nest joins & build virtual results.
select #a.a , #b.b , #ab.a AS "ab-a" , #ab.b as "ab-b"
from #ab
full join ( #a cross join #b )
on #ab.a = #a.a And #ab.b = #b.b
-- Expected Output:
/*
a b ab-a ab-b
----------- ----------- ----------- -----------
1 5 1 5
1 6 NULL NULL
2 5 NULL NULL
2 6 2 6
*/
go
drop table #a;
drop table #b;
drop table #ab;
go
This page was last updated on May 01, 2006 04:28 PM.