Lam Vu Lam Vu - 1 month ago 6
MySQL Question

SQL Query: For each item add multiple values

How to write a SQl query to create "NewTable" using Table1 and Table2 which the result look like "NewTable" below ?

Table1
EmmployeeID
| 1 |
| 2 |
| 3 |

Table2
AgencyID
| a |
| b |
| c |

NewTable
|EmmployeeID|AgencyID|
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | a |
| 3 | b |
| 3 | c |

Answer

Create the third table and then INSERT the values by doing a CROSS JOIN of Table1 and Table2

CREATE TABLE NewTable
( 
  EmployeeID int,
  AgencyID varchar,
);

INSERT INTO NewTable
select EmployeeID, AgencyID
from Table1 CROSS JOIN Table2
Comments