3

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
1

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 don't think it will work this way,even if A has more rows than B , after joining in above example in question the value in second column will be null after 4th row . – g4ur4v Aug 8 '16 at 10:19
0

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   
  • Please explain how it works. – Tulains Córdova Aug 8 '16 at 11:26
  • using the rownum as you suggested , I modified the rn of table A to fall in the same range to that of the table B and then used to them join. – g4ur4v Aug 8 '16 at 14:40
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.