Home > database >  How to assign multiple dates to multiple categories in SQL?
How to assign multiple dates to multiple categories in SQL?

Time:11-11

I have a query which will return 3 dates.

The query is

SELECT DISTINCT date FROM date_table

Output is

date
2020-02-10
2019-08-08
2017-09-06
2021-11-10 

I have another table called sales with only category in it.

Category
chocs
biscuits

I'm trying to assign those above 4 dates to every category. Like below data

category    date        
chocs       2020-02-10 
chocs       2019-08-08  
chocs       2017-09-06 
chocs       2021-11-10 
biscuits    2020-02-10
biscuits    2019-08-08
biscuits    2017-09-06
biscuits    2021-11-10

How to write a SQL query for this?

CodePudding user response:

Use a cross join:

SELECT s.category, d.date
FROM sales s
CROSS JOIN date_table d
ORDER BY s.category, d.date;
  • Related