CODEWITHSUNDEEP
databaseoracle-databasejoin
user5078583
user5078583

Reputation: 11

Outer Join Table in Oracle - Join separately by each date

Suppose I have some data and doing a query, I got something like this - 

-----------------------------
trun(date) | location | sum |
-----------------------------
14-June-11 | B        | 5   |
-----------------------------
13-June-11 | B        | 5   |
-----------------------------
14-June-11 | C        | 5   |
-----------------------------
13-June-11 | C        | 5   |
-----------------------------

SELECT TRUNC(DATE_TIME),MIN(LOCATION) AS LOCATION, SUM(CREDIT) AS SUM FROM 
(SELECT * FROM TABLE A
WHERE
 A.DATE_TIME >= TO_DATE('13/JUN/2011','dd/mon/yyyy')
 AND A.DATE_TIME <= TO_DATE('15/JUN/2011','dd/mon/yyyy'))
GROUP BY TRUNC(DATE_TIME), LOCATION

There's another table B where there's a location list 

----
A  |
----
B  |
----
C  |
----

I want something like this - 

-----------------------------
trun(date) | location | sum |
-----------------------------
14-June-11 | A        | 0   |
-----------------------------
14-June-11 | B        | 5   |
-----------------------------
14-June-11 | C        | 5   |
-----------------------------
13-June-11 | A        | 0   |
-----------------------------
13-June-11 | B        | 5   |
-----------------------------
13-June-11 | C        | 5   |
-----------------------------

I tried to use a right join with the table B, but I can't create 2 separate records for 14-June-11 and 13-June-11. Any advice or help will be greatly appreciated.

Upvotes: 1

Views: 78

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

There is no need to read tablea separately to get the distinct date_time values. Instead, this is a job for the often overlooked partitioned outer join.

Here is your answer using that feature. (The tables and inserts from William Robertson's answer will work to set this up).

select a.date_time
     , b.location
     , coalesce(a.asum,0) as asum
from  ( SELECT trunc(a.date_time) date_time, 
               a.location, 
               sum(a.credit) as asum 
        FROM tablea a 
        WHERE  a.date_time between date '2011-06-13' and date '2011-06-15' or a.date_time is null
        GROUP BY trunc(a.date_time), a.location ) a 
        -- This is the key part here...
        PARTITION BY (date_time)
right join tableb b on a.location = b.location
order by 1 desc, 2;

What that PARTITION BY keyword does is makes the outer join operate separately for each distinct value of date_time, creating null outer-join rows as needed for each one.

+-----------+----------+------+
| DATE_TIME | LOCATION | ASUM |
+-----------+----------+------+
| 14-JUN-11 | A        |    0 |
| 14-JUN-11 | B        |    5 |
| 14-JUN-11 | C        |    5 |
| 13-JUN-11 | A        |    0 |
| 13-JUN-11 | B        |    5 |
| 13-JUN-11 | C        |    5 |
+-----------+----------+------+

Upvotes: 3

William Robertson
William Robertson

Reputation: 16001

How about this:

with dates as
     ( select distinct trunc(date_time) as date_time from tablea )
select trunc(d.date_time)
     , b.location
     , coalesce(sum(credit),0) as sum
from   dates d
       cross join tableb b
       left join tablea a
            on  a.location = b.location
            and trunc(a.date_time) = d.date_time
where  a.date_time between date '2011-06-13' and date '2011-06-15' or a.date_time is null
group by d.date_time, b.location
order by 1 desc, 2;

Test data:

create table tablea
( date_time date
, location varchar2(1)
, credit number(1,0) );

create table tableb(location varchar2(1));

insert into tablea values (date '2011-06-14' + dbms_random.value, 'B', 2);
insert into tablea values (date '2011-06-14' + dbms_random.value, 'B', 3);
insert into tablea values (date '2011-06-14' + dbms_random.value, 'C', 5);

insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 1);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 2);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 2);

insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 1);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 2);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 2);

insert into tableb values('A');
insert into tableb values('B');
insert into tableb values('C');

Upvotes: 1

Related Questions