I am trying to use SQL to create a table that concatenates all dates from a specific range to all items in another table. See image for an example.

I have a solution where I can create a column of "null" values in both tables and join on that column but wondering if there is a more sophisticated approach to doing this.

Example image

I've tried the following: Added a constant value to each table Then I joined the 2 tables on that constant value so that each row matched each row of both tables. This got the intended result but I'm wondering if there's a better way to do this where I don't have to add the constant values:

SELECT c.Date_,k.user_email
FROM `operations-div-qa.7_dbtCloud.calendar_table_hours_st` c
JOIN `operations-div-qa.7_dbtCloud.table_key` k 
    ON c.match = k.match
ORDER BY Date_,user_email asc

CodePudding user response：

It's not a concatenation in the image given, Its a join

select t1.dates Date ,t2.name Person from table t1,table t2;

CodePudding user response：

Cross join should work for you:

It joins every row from both tables with each other. Use this when there is no relationship between the tables.

Did not test so syntax may be slightly off.

SELECT c.Date_,k.user_email
FROM `operations-div-qa.7_dbtCloud.calendar_table_hours_st` c
CROSS JOIN `operations-div-qa.7_dbtCloud.table_key` k      
ORDER BY Date_,user_email asc