CODEWITHSUNDEEP
sqloracle-databaseoracle11g
jbassking
jbassking

Reputation: 535

How can I get a join to fill in missing values?

Given the following table:

name             date           val1         val2       val3
--------------------------------------------------------------
A                11/20/2013      34           12         123
B                11/20/2013      657          3465       568 
A                11/21/2013      12           67         458
B                11/21/2013      524          45         3654  
C                11/21/2013      56           25         55432

How can I get results like this?

A                11/20/2013      34           12         123
B                11/20/2013      657          3465       568
C                11/20/2013      0            0          0 
A                11/21/2013      12           67         458
B                11/21/2013      524          45         3654 
C                11/21/2013      56           25         55432

I'm pretty sure this can be done but just can't figure it out.

Upvotes: 0

Views: 109

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

Another, slightly shorter, slightly faster, approach, to produce output you are after, would be using partition outer join (10g onward):

with t2 as(
  select distinct name1
    from t1
)
select t2.name1
     , t1.date1
     , nvl(t1.val1, 0) as val1
     , nvl(t1.val2, 0) as val2
     , nvl(t1.val3, 0) as val3
  from t1 
  partition by (t1.date1)
  right join t2
     on (t1.name1 = t2.name1)

Result:

NAME1 DATE1            VAL1       VAL2       VAL3
----- ---------- ---------- ---------- ----------
A     11/20/2013         34         12        123 
B     11/20/2013        657       3465        568 
C     11/20/2013          0          0          0 
A     11/21/2013         12         67        458 
B     11/21/2013        524         45       3654 
C     11/21/2013         56         25      55432

SQLFiddle Demo

Upvotes: 1

Taryn
Taryn

Reputation: 247690

In order to include names and dates that don't have entries, you need to first generate a list of distinct names and dates.

I would get the list by using a CROSS JOIN similar to the following:

select distinct t.name, c.date
from yourtable t
cross join
(
  select date
  from yourtable
) c

See SQL Fiddle with Demo. Once you have this list of all names and dates, then you can use a LEFT JOIN back to your table to get the full list including those with zero values.

with cte as
(
  select distinct t.name, c.date
  from yourtable t
  cross join
  (
    select date
    from yourtable
  ) c
)
select d.name,
  d.date,
  coalesce(t.val1, 0) val1,
  coalesce(t.val2, 0) val2,
  coalesce(t.val3, 0) val3  
from cte d
left join yourtable t
  on d.name = t.name
  and d.date = t.date
order by d.date, d.name

See SQL Fiddle with Demo. This will give a result:

| NAME |                            DATE | VAL1 | VAL2 |  VAL3 |
|------|---------------------------------|------|------|-------|
|    A | November, 20 2013 00:00:00+0000 |   34 |   12 |   123 |
|    B | November, 20 2013 00:00:00+0000 |  657 | 3465 |   568 |
|    C | November, 20 2013 00:00:00+0000 |    0 |    0 |     0 |
|    A | November, 21 2013 00:00:00+0000 |   12 |   67 |   458 |
|    B | November, 21 2013 00:00:00+0000 |  524 |   45 |  3654 |
|    C | November, 21 2013 00:00:00+0000 |   56 |   25 | 55432 |

Upvotes: 3

Related Questions