How to join 2 tables without common column?

Question

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 :)

Andreas Fester · Accepted Answer · 2016-09-16T09:29:19.980

10

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

edited Sep 16 '16 at 09:29

answered Nov 29 '12 at 07:15

Andreas Fester

35,119
7
92
115

thnx for the clarification :) – Anupam Roy Nov 29 '12 at 07:30
what about cross join? – Dima Dz Sep 16 '16 at 08:54
1

@DimaDz What I showed in the example **is** a cross join. http://stackoverflow.com/questions/11861417/what-is-the-difference-between-cartesian-product-and-cross-join – Andreas Fester Sep 16 '16 at 09:30
sorry, my fault :-) – Dima Dz Sep 16 '16 at 19:43

score 0 · Answer 2 · answered Nov 29 '12 at 07:15

0

Try this:

SELECT a.usercol, b.menucol FROM UserCode a JOIN SubMenuID b

answered Nov 29 '12 at 07:15

Saharsh Shah

27,975
8
43
82

score 0 · Answer 3 · answered Nov 29 '12 at 07:20

0

select * from usercode,submenuid
order by usercol;

answered Nov 29 '12 at 07:20

Ajith Sasidharan

1,135
6
7

How to join 2 tables without common column?

3 Answers3

Linked