1

I have 3 tables Channels, DateSelection and History. Channels and DateSelection are updated as needed and are typically like below Channels Table - 

  CHID    CHNAME    CHLOC                     

  1        BARRY     NULL                           

  2        TOM       NULL

  3        SHIRLY    NULL

DateSelection Table

WEEKCOMMENCING       WEEKENDING

  01/01/2014        03/01/2014

History Is Empty with columns - Channel - Date - User

What i would like to do is run a query or trigger mysql or php etc that would make rows of

Channel   -    Date    -     User

  Barry         01/01/2014

  Barry         02/01/2014

  Barry         03/01/2014

  Tom           01/01/2014

  Tom           02/01/2014

  Tom           03/01/2014

 Shirly         01/01/2014

 Shirly         02/01/2014

 Shirly         03/01/2014

Essentially every possible combination?

I have got it sort of working with the following in a view. and doing each day independently

INSERT INTO FCHISTORY

(CHNAME, DATE)

SELECT Channels.CHNAME, FCCURRENTDATES.DATE

FROM Channels

CROSS JOIN FCCURRENTDATES

However this creates duplicates constantly. What can I add to only have 1 of each matching combination of CHNAME and DATE

Can someone help me out here? I dont mind doing it in a one day at a time way but there is actually a cpl hundred channels. Thanks!

Macro
  • 23
  • 3
  • Do you mean that there will be an output row for reach month that is between weekcommencing and weekending? – tofutim Jan 02 '14 at 05:55
  • an output row that saves to the HISTORY table for each day that is within the weekcommencing and ending. Australian we go DD/MM/YYYY :) – Macro Jan 02 '14 at 05:59
  • lol, that makes more sense! see http://stackoverflow.com/questions/15223032/mysql-display-all-date-in-between-range – tofutim Jan 02 '14 at 06:03
  • or maybe http://stackoverflow.com/questions/2157282/generate-days-from-date-range - once you have this you can do a JOIN on the tables – tofutim Jan 02 '14 at 06:05
  • read both of them in search earlier, thanks though. I also need to have the Channel field filled out so that for every channel there is every date – Macro Jan 02 '14 at 06:10
  • for the latter, just JOIN your table to the dates – tofutim Jan 02 '14 at 06:15

1 Answers1

1

This should do the trick:

SELECT CHNAME AS Channel, a.Date, '' AS User
FROM Channels c
JOIN DateSelection ds
JOIN (
  select curdate() + INTERVAL 100 DAY - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
  from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a ON a.Date BETWEEN ds.WEEKCOMMENCING AND ds.WEEKENDING
ORDER BY CHID, a.Date

See sqlfiddle - http://sqlfiddle.com/#!2/87a5f/19

Note that you might adjust the date range for selection depending on whether you are looking into the future or past. Right now the range is 900 days before the curdate and 100 days after. Adjust as needed.

tofutim
  • 22,664
  • 20
  • 87
  • 148