Skip to content
Advertisement

How to join two unrelated table in MySQL?

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..

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement