CODEWITHSUNDEEP
mysql
George
George

Reputation: 1114

Is It Possible To Join Two Unrelated Tables In MySQL?

I have two unrelated tables but i want to join them into one query, is that possible?

This is how I did it using cross join but it did not work

table 1
|   ID  |   Amount  |
|   1   |    20     |
|   2   |    10     |
|   3   |    21     |
|   4   |    50     |


table 2
|   ID      |   Paid Value      |
|   011     |      5            |
|   052     |      2            |

//My tried Query

SELECT
    a.`Amount`,
    b.`Paid Value`
FROM
    `table 1` a 
CROSS JOIN
    `table 2` b

This is what i get in return using the above query

|   ID  |   Amount  | Paid Value    | 
|   1   |    20     |   5           |
|   2   |    10     |   2           |
|   3   |    21     |   5           |
|   4   |    50     |   2           |

However this is my expected results

|   ID  |   Amount  | Paid Value    | 
|   1   |    20     |   5           |
|   2   |    10     |   2           |
|   3   |    21     |   0           |
|   4   |    50     |   0           |

Upvotes: 1

Views: 664

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You want to join by some implicit row number. Let me assume that this is based on the ordering of the ids. You can use variables to calculate the row number and then use that for the join:

select t1.id, t1.amount, coalesce(t2.paidvalue, 0)
from (select t1.*, (@rn := @rn + 1) as rn
      from table1 t1 cross join
           (select @rn := 0) vars
      order by id
     ) t1 left join
     (select t2.*, (@rn2 := @rn2 + 1) as rn
      from table1 t2 cross join
           (select @rn2 := 0) vars
      order by id
     ) t2
     on t1.rn = t2.rn;

Upvotes: 2

Related Questions