I have a Employee info (one row per employee) table and a Calendar table (one row per day).
I want a results table that has one row per day for each employee.
employee table
| EmpID | EmpName |
|---|---|
| 0001 | Jill |
| 0002 | Bill |
| 0003 | Steve |
calendar table
| Date | Month |
|---|---|
| 01/01/2022 | January |
| 01/02/2022 | January |
| 01/03/2022 | January |
result table
| Date | EmpName |
|---|---|
| 01/01/2022 | Jill |
| 01/01/2022 | Bill |
| 01/01/2022 | Steve |
| 01/02/2022 | Jill |
| 01/02/2022 | Bill |
| 01/02/2022 | Steve |
| 01/03/2022 | Jill |
| 01/03/2022 | Bill |
| 01/03/2022 | Steve |
>Solution :
A cross join is what you want:
SELECT * FROM EMPLOYEE_TABLE
CROSS JOIN CALENDAR_TABLE;
A cross join literally just multiplies the number of rows in the first table with the number of rows in the second table.