0

Here's my problem. I have an SQL statement: 

SELECT * FROM table1 WHERE week_no >= 1 AND week_no <= 5

But the result only returns the following result.

Col1 | Col2 | Week_No
 A      B       1
 C      D       2
 D      E       4
 F      G       5

I tried pushing to the array result of the query the "week_no" #3 with default values but it seems it doesn't work.

I would like to know if there's a much easier way in doing it on SQL? Like inserting:

Col1 | Col2 | Week_No
 R      R       3

R-R as default values.

Thanks for all the help.

Rav
  • 1,327
  • 3
  • 18
  • 32
  • 1
    do you have a numbers table in the database? – Vamsi Prabhala Jun 02 '17 at 02:20
  • @vkp number of weeks in a table? yes I do have but the structure is week_start and week_end (in the example, 1 and 5 are the values). – Rav Jun 02 '17 at 02:30
  • look at this [answer](https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql) and create a numbers table..then what you need to do is a `left join` on to that.. – Vamsi Prabhala Jun 02 '17 at 02:39

2 Answers2

3

You want an outer join. Here is a typical approach:

SELECT COALESCE(t1.col1, 'R') as col1, COALESCE(t1.col2, 'R') as col2,
       n.n as week_no
FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
      SELECT 4 UNION ALL SELECT 5
      ) n LEFT JOIN
      table1 t1
      ON t1.week_no >= n.n AND week_no <= n.n;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You're too fast for me :) – Barmar Jun 02 '17 at 02:22
  • he's still asleep though! you both are legends – RoMEoMusTDiE Jun 02 '17 at 02:23
  • But the week numbers are dynamic and not static. Any suggestions. :) (Upvoted your answer though) :) – Rav Jun 02 '17 at 02:23
  • @Ronald . . . That is a bit more challenging. If you have a numbers table -- or even a table with enough rows -- then you can use that to generate the week values. – Gordon Linoff Jun 02 '17 at 02:38
  • @GordonLinoff, yeah I realized that it's a lot harder than I thought. I already considered the idea of making a weeks table then just left joining it but I have to repeat everything. LOL. – Rav Jun 02 '17 at 02:41
0

Please try to check the data type in each column that you will insert to your table or to your array.

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
JP Dolocanog
  • 451
  • 3
  • 19