0

I have a calendar table which days may be filled with meetings; my main SQL returns these results : 

DAY  | MEETINGS 
--------------
20   |    2
21   |    2
25   |    1

I'm trying to find a way to have ALL days of the month listed in the results, and the idea is to try something like this : 

SELECT (@day := @day + 1) AS `Day` FROM (SELECT @day := 0) AS `Month`;

and add it in the proper way inside the main SQL, but it is not the right answer I'm expecting, so I'm asking if there is a way to put a do/loop/while command INSIDE the SELECT statement, something like this : 

SELECT (DO @day := @day + 1 UNTIL @day <= 31) AS `Day` FROM (SELECT @day := 0) AS `Month`;
F. Bas
  • 31
  • 7
  • 5
    Do you have a calendar days table available? If not, I would create one. https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql You `create` the calendar table and `join` the dates from your other table on it. – justiceorjustus Dec 20 '17 at 14:19
  • A `CASE` statement should suffice I think. – Jacob H Dec 20 '17 at 14:19
  • alas the CASE works only on collected records and it can't generate rows without having a source to link to. i want to have the missing days without any table as source and list them together with the SQL result – F. Bas Dec 20 '17 at 14:23
  • No it's not possible to have a loop in a select statement, and you can only have loops within stored programs (functions,procedures,triggers,events) – P.Salmon Dec 20 '17 at 14:30
  • yes Kaddath the "code" way is the way to follow but i was trying to move the PHP to MYSQL in order to simplify my page. – F. Bas Dec 20 '17 at 14:33
  • Good idea to do it with stored procedures too. Thank you very much for your help. – F. Bas Dec 20 '17 at 14:36

0 Answers0