Results 1 to 3 of 3

Thread: Null record

  1. #1
    Join Date
    Jun 2004
    Posts
    12

    Question Unanswered: Null record

    Hi all,
    here is my problem: I have two tables, one containing dates and the other dates + data which I'd like to join to get Null rows when no data is found. I would like to get a recordset holding tha values based upon their Id using a left join to fill with NULL when data is missing. I tried with the query below, but I cannot get the Null record showing up. It works without the WHERE clause, but then I'll get all the rows. Any suggestions would be greatly appreciated!
    Thank you very much !

    Table master
    'Fulldate'
    '2004-08-10 08:00:00'
    '2004-08-10 07:00:00'
    '2004-08-10 06:00:00'


    Table cab_a
    'Fulldate','Id','MeanValue'
    '2004-08-10 08:00:00','4','0.9'
    '2004-08-10 08:00:00','3','0.5'
    '2004-08-10 08:00:00','2','0.01'
    '2004-08-10 08:00:00','1','0.14'
    ............. missing ................
    '2004-08-10 06:00:00','4','0.2'
    '2004-08-10 06:00:00','3','0.2'
    '2004-08-10 06:00:00','2','0.02'
    '2004-08-10 06:00:00','1','0.14'

    SQL query
    SELECT master.Fulldate, MeanValue FROM master left join cab_a using (Fulldate) WHERE id=3 ORDER BY Fulldate DESC;
    '2004-08-10 06:00:00','0.2'
    '2004-08-10 08:00:00','0.5'

    ' The recordset I would like to get
    '2004-08-10 06:00:00','0.2'
    '2004-08-10 07:00:00','NULL'
    '2004-08-10 08:00:00','0.5'

    Regards,
    Paolo Saudin

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    This is a perfect example to illustrate the difference between ON and WHERE.
    Code:
    SQL> create table a
      2  (
      3  name number(2)
      4  );
    
    Table created.
    
    SQL> insert into a values (1);
    
    1 row created.
    
    SQL> insert into a values (2);
    
    1 row created.
    
    SQL> create table b as select * from a;
    
    Table created.
    
    SQL> alter table b add value number(2);
    
    Table altered.
    
    SQL> update b set value = name*10;
    
    2 rows updated.
    
    SQL> select a.name, NVL(b.name, -1), NVL(b.value, -1)
      2  from a
      3  LEFT OUTER JOIN
      4  b ON
      5  a.name = b.name
      6  where b.value = 10;
    
          NAME       NAME      VALUE
    ---------- ---------- ----------
             1          1         10
    
    SQL> select a.name, NVL(b.name, -1), NVL(b.value, -1)
      2  from a
      3  LEFT OUTER JOIN
      4  b ON
      5  a.name = b.name AND
      6  b.value = 10;
    
          NAME NVL(B.NAME,-1) NVL(B.VALUE,-1)
    ---------- -------------- ---------------
             1              1              10
             2             -1              -1
    
    SQL> select a.name, NVL(V.name, -1), NVL(V.value, -1)
      2  from a
      3  LEFT OUTER JOIN
      4  (Select b.name, b.value
      5  from b
      6  where value = 10) V ON
      7  a.name = V.name;
    
          NAME NVL(V.NAME,-1) NVL(V.VALUE,-1)
    ---------- -------------- ---------------
             1              1              10
             2             -1              -1
    Thank You.
    Last edited by r123456; 08-10-04 at 08:34.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jun 2004
    Posts
    12

    Thumbs up

    It works perfectly,
    Thank you very much !!
    Paolo

Posting Permissions

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