Get a value, even if it's null

Databases
1

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

2

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

3

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?

4 
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
5

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