SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get a value, even if it's null

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first, create a numbers table --
    Code:
    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...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •