Get a value, even if it's null

[BigBadBurrow]

Hello, I was wondering if someone could help me. I need to get data from a table which is currently stored like this:

id
51 2013-02-01 10
98 2013-02-01 1
51 2013-02-02 9
98 2013-02-02 2
51 2013-02-03 8
51 2013-02-04 7
51 2013-02-05 6


For each id/date I need a value, even if it's null, so the query gets data like this:

id
51 2013-02-01 10
98 2013-02-01 1
51 2013-02-02 9
98 2013-02-02 2
51 2013-02-03 8
98 2013-02-03 null
51 2013-02-04 7
98 2013-02-04 null
51 2013-02-05 6
98 2013-02-05 null

any ideas?

Thanks,

BBB

[r937]

first, create a numbers table --

CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers ( n ) VALUES 
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)...

then just select from this table as follows...

SELECT DATE('2013-02-01') 
         + INTERVAL n DAY AS the_date
  FROM numbers
 WHERE DATE('2013-02-01') + INTERVAL n DAY
         < DATE('2013-03-01')

now you can use this "on the fly" table as the left table in a LEFT OUTER JOIN to your data table

any dates that are not matched in your data table will generate a NULL

[BigBadBurrow]

Thanks Rudy, I don't think I was clear and you thought I wanted to output a null if I didn't have 'any' data for a particular date? I actually need to output a null if I don't have a value for the id/date combination.

Let me elaborate:


team
------------
(id name)
51 Team1
98 Team2


ranking
------------
(id date pos)
51 2013-02-01 10
98 2013-02-01 1
51 2013-02-02 9
98 2013-02-02 2
51 2013-02-03 8
51 2013-02-04 7
51 2013-02-05 6

You see I don't have a pos for 98 on 2013-02-03, 2013-02-04, 2013-02-05?

I need to output:

query
------------
(id date pos)
51 2013-02-01 10
98 2013-02-01 1
51 2013-02-02 9
98 2013-02-02 2
51 2013-02-03 8
98 2013-02-03 null
51 2013-02-04 7
98 2013-02-04 null
51 2013-02-05 6
98 2013-02-05 null

Any idea how I might do that?

[r937]

SELECT team.id
     , dates.the_date
     , ranking.pos
  FROM team
CROSS
  JOIN ( SELECT DATE('2013-02-01')
                  + INTERVAL n DAY AS the_date
           FROM numbers
          WHERE DATE('2013-02-01') + INTERVAL n DAY
              < DATE('2013-03-01')
       ) AS dates    
LEFT OUTER
  JOIN ranking
    ON ranking.id = team.id
   AND ranking.date = dates.the_date

[BigBadBurrow]

Fantastic Rudy. I don't think I'd have ever got that. Thanks once again for your help - it's not the first time you've helped me with a tricky bit of SQL