join and repeat value from a column of another table

I have two table A and B

Table A

C1
----
11
232
454
65
78
87
45
87
98
99

Table B

C2
----
c1
c2
c3
c4

How can I join these table to get output like

C1, C2
--------
11  ,c1
232 ,c2
454 ,c3
65  ,c4
78  ,c1
87  ,c2
45  ,c3
87  ,c4
98  ,c1
99  ,c2

Answers 3

  • Below query seems to work,If anybody has better solution please comment.

    select
        c1,c2
    from
        (select mod(rownum,(select count(1) from r2))+1 rn,customer_Key as c1 from r1) t1, 
        (select rownum rn,customer_Key as c2 from r2) t2 
        where 
        t1.rn=t2.rn   
    

  • Since the tables have no column in common, we'll have to use the rownum pseudocolumn which is asigned to every row in a resultset. Do not confuse with rowid which is hash-like and exists phisycally in the table. Since rowids go 1,2,3...n, they provide a way to join two unrelated tables.

    select
        t1.c1,
        t2.c2
    from
        (select rownum rn,c1 from a) t1 left join 
        (select rownum rn,c2 from b) t2 on (t1.rn=t2.rn)
    

    NOTE:

    I assumed A had more rows than B so I made a left join. You can turn things around should B have more rows than A.


  • I would take the modulus of the row numbers, using a 0 base instead of the default 1. This example assumes we know A contains more rows than B. A window function is used to pull the maximum row number with one logical read rather than calling COUNT(*) separately.

      WITH a_ AS (SELECT c1, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM a),
           b_ AS (SELECT c2, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM b)
    SELECT a_.c1, b_.c2
      FROM a_
     INNER JOIN b_
        ON b_.rn = MOD(a_.rn, b_.max_rn)
     ORDER BY a_.rn ASC;
    

Related Questions