I have two unrelated tables and I need to join in a single row as expected output below. I tried below query and doesn’t work. How to make join these tables??
Table 1
| col1 | amount| | a | 200 | | b | 100 | | c | 300 | | d | 500 |
Table 2
| col2 | amount| | e | 900 | | f | 800 |
Expected Output:
| col1 | Amount | col2 | Amount | a | 200 | e | 900 | | b | 100 | f | 800 | | c | 300 | | d | 500 |
I have tried this query
set @a =0; set @b=0; SELECT (@a:=@a + 1) AS table1_serial_no, (@b:=@b + 1) AS table2_serial_no, table1.col1, table1.Amount, table2.col2, table2.Amount FROM table1 left outer JOIN table2 ON table1_serial_no = table2_serial_no;
Advertisement
Answer
select * from ( select @rn:=@rn + 1 rn from t cross join (select @rn:=0) r union select @rn:=@rn + 1 from t1 ) allrows left join (select col1,amount, @rn1:=@rn1 + 1 rn from t cross join (select @rn1:=0) r) t on t.rn = allrows.rn left join (select col2,amount, @rn2:=@rn2 + 1 rn from t1 cross join (select @rn2:=0) r) t1 on t1.rn = allrows.rn where col1 is not null or col2 is not null;
where the all rows sub query works out the allocates a row number to the max possible number of rows which is then used later to join to the row numbers allocated to table1 and table2. NOTE there is nothing to order by so the results are not ordered..