Populate dates for each outlet

Question

From the link Easiest way to populate a temp table with dates between and including 2 date parameters

I generated a table

Date
2012-01-01
2012-01-02
..
2012-01-31

I need to ask how to combine with my outlet table?

Say I have an outlet table

ID | Name
1  | Outlet A
2  | Outlet B
3  | Outlet C

I want to combine generated dates table and outlet table to be like below, so each date has all of the outlets

Date       | Outlet
2012-01-01 | Outlet A
2012-01-01 | Outlet B
2012-01-01 | Outlet C
2012-01-02 | Outlet A
2012-01-02 | Outlet B
2012-01-02 | Outlet C
...
2012-01-31 | Outlet A
2012-01-31 | Outlet B
2012-01-31 | Outlet C

score 4 · Answer 1 · answered Jul 08 '13 at 07:16

4

You can use CROSS JOIN:

SELECT d.Date, o.Outlet
FROM DatesTable d
CROSS JOIN OutletsTable o

answered Jul 08 '13 at 07:16

Nenad Zivkovic

18,221
6
42
55

Great! this also has the right result. Is there any difference with the other answer below? – Akhtar Jul 08 '13 at 07:41
@Akhtar It's a syntax difference. Performance and results are the same. Using `CROSS JOIN` is SQL-92 standard, while the other answer is using old style joins - SQL-89 syntax. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Nenad Zivkovic Jul 08 '13 at 07:49

Hiren Dhaduk · Accepted Answer · 2013-07-08T07:28:28.040

1

you can do it by simple query :

select a.date , b.Name
from dates as a , outlets as b

it will return result you want . this query will return Cartesian Product of tables which are in from clause . check article for more detail .

edited Jul 08 '13 at 07:28

answered Jul 08 '13 at 07:17

Hiren Dhaduk

2,760
2
19
21

Thanks, very simple, why did I never think about that. – Akhtar Jul 08 '13 at 07:35

Populate dates for each outlet

2 Answers2