Anupam Roy Anupam Roy - 2 months ago 10
MySQL Question

How to join 2 tables without common column?

i have 2 tables

User Code SubMenuID

usercol menucol
----------- -------------
AB Sub-01
Alam Sub-02
CSRL


i want to show them like this

usercol menucol
---------- ------------
AB Sub-01
AB Sub-02
Alam Sub-01
Alam Sub-02
CSRL Sub-01
CSRL Sub-02


How can i get this using sql query? It would be very helpful :)

Answer

Since the tables are not related by a foreign key relationship, you can not join them - what you want as a result, is the Cartesian product from the two tables. This is achieved by selecting from both tables without any additional join condition (this is also called a cross join):

mysql> SELECT * FROM userCode, SubMenuId;

This query combines all rows from the first table with all rows from the second table.

+---------+---------+
| usercol | menucol |
+---------+---------+
| AB      | Sub-01  |
| AB      | Sub-02  |
| Alam    | Sub-01  |
| Alam    | Sub-02  |
| CSRL    | Sub-01  |
| CSRL    | Sub-02  |
+---------+---------+