Joining tables (without a clause)

Question

I want to join to tables and get the following output

Table1

TestId1
----------
one
two
three
four
five
six
seven
eight

Table2

TestId2
----------
fiftythree
fiftyfour
fiftytwo
fiftyfive
fiftyone

I want Table3 as my output with all rows from table1 and the first rows from table2 until there are no more rows left and then they should start repeating.

As an alternate answer, they can also be assigned randomly.

TestId1        TestId2   
----------     ----------
one           fiftythree
two           fiftyfour 
three         fiftytwo  
four          fiftyfive 
five          fiftyone  
six           fiftythree
seven         fiftyfour 
eight         fiftytwo

Answer 1

You might want to try JOINing on ROWNUM.

http://www.adp-gmbh.ch/ora/sql/rownum.html

Answer 2

Try this:

SELECT t1.name, t2.name FROM
(
    SELECT (ROW_NUMBER() OVER(ORDER BY name)-1)%(SELECT COUNT(*) FROM test2) AS j,* 
    FROM test1
) t1
INNER JOIN 
(
    SELECT ROW_NUMBER() OVER(ORDER BY name)-1 AS j,* 
    FROM test2
) t2 ON t1.j = t2.j
ORDER BY t1.name

In details:

SELECT (ROW_NUMBER() OVER(ORDER BY name)-1) AS j,* 
FROM test1

Returns:

0 | one
1 | two
2 | three
3 | four
4 | five
5 | six
6 | seven
7 | eight

This:

SELECT ROW_NUMBER() OVER(ORDER BY name)-1 AS j,* 
FROM test2

Returns:

0 | fiftythree
1 | fiftyfour
2 | fiftytwo
3 | fiftyfive
4 | fiftyone

All you have to do is to divide (% - I don't know the english name of this) the first column of the longest table by the number of elements in the shorter:

SELECT (ROW_NUMBER() OVER(ORDER BY name)-1)%(SELECT COUNT(*) FROM test2) AS j,* 
FROM test1

Returns:

0 | one
1 | two
2 | three
3 | four
4 | five
0 | six
1 | seven
2 | eight

All you have to do now is to join both tables on the first column.

This solution is made by using one query only, but it assumes that in table1 there are more elements then in table2. If you don't like this solution I've just gave you good basis to write store procedure.

Answer 3

Your alternate solution is the only one

SELECT
    TestID1,
    TestID2
FROM
    (SELECT COUNT(*) AS Count1 FROM Table1) C1 --one row
    CROSS JOIN
    (SELECT COUNT(*) AS Count2 FROM Table2) C2 --one row
    CROSS JOIN
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY TestID1) AS Rank1,
        TestID1,
    FROM
        Table1
    ) t1
    JOIN
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY TestID1) AS Rank2,
        TestID2,
    FROM
        Table2
    ) t2 ON
         t1.Rank1 % CASE WHEN C1.Count1 > C2.Count2 THEN C2.Count2 ELSE 2000000000 END
         =
         t2.Rank2 % CASE WHEN C2.Count2 > C1.Count1 THEN C1.Count1 ELSE 2000000000 END
ORDER BY
    t1.Rank1, t2.Rank2

Answer 4

I'm think all solutions by one select is really ugly. I'm think better use stored procedure with two cursors (over each table)

Answer 5

This here works and has only one cursor:

if exists(select object_id('tempdb..#TestId1')) drop table #TestId1

if exists(select object_id('tempdb..#TestId2')) drop table #TestId2

if exists(select object_id('tempdb..#result')) drop table #result

create table #TestId1(col_1 varchar(100))

create table #TestId2(col_2 varchar(100))

create table #result (col_1 varchar(100), col_2 varchar(100))

set rowcount 0

insert into #TestId1(col_1 ) select col='one' union all select col='two' union all select col='three' union all select col='four' union all select col='five' union all select col='six' union all select col='seven' union all select col='eigh'

insert into #TestId2(col_2 ) select col='fiftythree' union all select col='fiftyfour' union all select col='fiftytwo' union all select col='fiftyfive' union all select col='fiftyone'

DECLARE @sectblcnt int select @sectblcnt=count(*) from #TestId2

DECLARE @sectableNo int

DECLARE @rowno int

declare @col_1 varchar(100), @col_2 varchar(100)

set @rowno=0

DECLARE curs CURSOR FOR SELECT col_1 FROM #TestId1

OPEN curs

FETCH NEXT FROM curs INTO @col_1

WHILE @@FETCH_STATUS = 0 BEGIN set @rowno=@rowno+1;

set @sectableNo = @rowno % @sectblcnt
set rowcount @sectableNo

select @col_2=col_2 from #TestId2

insert into #result(col_1, col_2)
 values(@col_1, @col_2)

FETCH NEXT FROM curs 
INTO @col_1

END

CLOSE curs

DEALLOCATE curs

set rowcount 0

select * from #result

Answer 6

I prefer stochastic processes, so went for the [pseudo-]random solution ;-)

This too requires "inducing" a row number on Table2, but the join with Table1 is driven by some hash [modulo the number of rows of Table2] on some column(s) of Table1 (doesn't have to be TestId1).

SELECT T1.TestId1, T2.TestId2 
FROM Table1 T1
JOIN (
  SELECT (ROW_NUMBER() OVER(ORDER BY TestId2) - 1) AS RowNum, TestId2 
  FROM Table2
) T2 ON ABS(HashBytes ('MD5', T1.TestId1) % (SELECT COUNT(*) FROM Table2))
        = T2.RowNum
ORDER BY t1.TestId1