SQLServer join two tables

Question

Hi guys, I've gotta question for you, I'm getting hard times trying to combine two tables, I can't manage to find the correct query.

I have two tables: T1: 1column, Has X records T2: 1column, Has Y records Note: Y could never be greater than X but it often lesser than this one

I want to join those tables in order to have a table with two columns t3: ColumnFromT1, columnFromT2.

When Y is lesser than X, the T2 field values gets repeated and are spread over all my other values, but I want to get NULL when ALL the columns from T2 are used.

How could I achieve that? Thanks

Answer 1

You are looking for a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) eg . T1 LEFT JOIN T2

say they both have column CustomerID in common

SELECT * 
  FROM T1
        LEFT JOIN
       T2 on t1.CustomerId = T2.CustomerId

This will return all records in T1 and those that match in T2 with nulls for the T2 values where they do not match.

Make sure you are joining the tables on a common column (or common column set if more than one column are necessary to perform the join). If not, you are doing a cartesian join ( http://ezinearticles.com/?What-is-a-Cartesian-Join?&id=3560672 )

Answer 2

You could give each table a row number in a subquery. Then you can left join on that row number. To recycle rows from the second table, take the modulus % of the first table's row number.

Example:

select  Sub1.col1
,       Sub2.col1
from    (
        select  row_number() over (order by col1) as rn
        ,       *
        from    @T1
        ) Sub1
left join
        (
        select  row_number() over (order by col1) as rn
        ,       *
        from    @T2
        ) Sub2
on      (Sub1.rn - 1) % (select count(*) from @T2) + 1 = Sub2.rn

Test data:

declare @t1 table (col1 int)
declare @t2 table (col1 datetime)

insert @t1 values (1), (2), (3), (4), (5)
insert @t2 values ('2010-01-01'), ('2012-02-02')

This prints:

1        2010-01-01
2        2012-02-02
3        2010-01-01
4        2012-02-02
5        2010-01-01