CODEWITHSUNDEEP
sqloracle-databaseleft-join
monitorjbl
monitorjbl

Reputation: 4350

Oracle SQL left join with null values copied

I'm not sure how to ask this question in a way that Google will understand. I want to left join two tables and have the blank fields filled in. Here's what I'm working with:

tbl1
| user | date  | usage |
| us1  | 01/12 |  503  |
| us1  | 02/12 |  245  |
| us1  | 03/12 |  465  |
| us2  | 02/12 |  327  |
| us2  | 03/12 |  204  |
| us3  | 02/12 |  156  |

tbl2
| dates | avg_use |
| 01/12 |   345   |
| 02/12 |   426   |
| 03/12 |   502   |

Desired output
| user | date  | usage  |  pct_of_avg  |
| us1  | 01/12 |  503   |     1.45     |
| us1  | 02/12 |  245   |     .58      |
| us1  | 03/12 |  465   |     .93      |
| us2  | 01/12 | (null) |      0       |
| us2  | 02/12 |  327   |     .95      |
| us2  | 03/12 |  204   |     .41      |
| us3  | 01/12 | (null) |      0       |
| us3  | 02/12 |  156   |     .37      |
| us3  | 03/12 | (null) |      0       |

I understand JOINs pretty well, so I'm aware that a typical JOIN will not be able to do this since the data in tbl1.user won't exist. Is there a way to do this in SQL at all? Bonus points for giving what I'm trying to do a name, because it would help a lot just to know how to Google this :)

Upvotes: 0

Views: 1779

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27261

If your Oracle version 10g or higher you can use partition by extension of outer join to fill in gaps in data. You will need less code. It'll be simpler, faster.

SQL> select t.user1
  2       , to_char(t1.dates, 'mm/yy') as date1
  3       , t.usage1
  4       , round((nvl(t.usage1, 0)/t1.avg_use), 2)  as pct_of_avg
  5   from tbl1 t
  6   partition by(t.user1)
  7   right join tbl2 t1
  8      on (t.date1 = t1.dates)
  9  ;

USER1 DATE1     USAGE1 PCT_OF_AVG
----- ----- ---------- ----------
us1   01/12        503       1.46
us1   02/12        245       0.58
us1   03/12        465       0.93
us2   01/12                     0
us2   02/12        327       0.77
us2   03/12        204       0.41
us3   01/12                     0
us3   02/12        156       0.37
us3   03/12                     0

9 rows selected

Upvotes: 2

Lamak
Lamak

Reputation: 70668

This should do it:

SELECT U.user, T2.dates date, T1.usage, T1.usage/T2.avg_use pct_of_avg  
FROM (SELECT DISTINCT user FROM tbl1) U
CROSS JOIN tbl2 T2
LEFT JOIN tbl1 T1
    ON T2.date = T1.date  AND U.user = T1.user

Upvotes: 3

Related Questions