CODEWITHSUNDEEP
sqlsql-server
Vimal
Vimal

Reputation: 101

Joining 'with' clause result with a table when 'with' clause has no data

I'm working on a query which has one with clause (view) and this is joined with a table using INNER/OUTER join. The issue is when the table doesn't have data, the join gets failed and no result is returned. However, the view has data.

I've used an INNER join which is failed in the first case. Then I tried with Left Outer Join which also doesn't work. Actually, the main query is quite complex, so I presented the problem in below code snippet. I'm expecting the answer in this format only

with myView as (Select mp.Id, sum(mp.Quantity) counts from Map mp where 
mp.bizDate='22-Jan-2019' group by mp.Id)

Select m.Id, mv.counts, (mv.counts - sum(m.Quantity)) from Map m 
LEFT OUTER JOIN myView mv ON 
m.id=mv.Id where m.bizDate='25-Jan-2019' group by m.Id, mv.counts

The issue is when Map table has no records it doesn't return any data, even if myView is having records. I need records in any case if one of them has data (either of myView or Map).

Sample Data:

Table: Map

Id          Quantity               BizDate
A            100                   22-Jan-2019
A            300                   22-Jan-2019
A            300                   25-Jan-2019
B            100                   22-Jan-2019
B            200                   25-Jan-2019
C            500                   22-Jan-2019
D            300                   25-Jan-2019

The Expected Output shoud be:

Id        Counts               (counts-Quantity)
A         400                       100         --> (400-300)
B         100                      -100         --> (100-200)
C         500                       500         --> (500-NA)
D         0                        -300         --> (NA-300)

Upvotes: 0

Views: 1216

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use Conditional Aggregation to calculate the data for each desire date.

SQL DEMO

with myView as ( 
    Select mp.Id, 
           sum( CASE WHEN mp.bizDate='22-Jan-2019' 
                     THEN mp.Quantity
                     ELSE 0 
                END ) counts,
           sum( CASE WHEN mp.bizDate='25-Jan-2019' 
                     THEN mp.Quantity
                     ELSE 0 
                END ) quantity    
    from Map mp       
    group by mp.Id
)    
SELECT mv.Id, 
       counts,
       counts - quantity
FROM myView mv;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to change the structure of the code a little bit, by splitting the conditions.

Basically, if you are concerned that MyView has no rows, then:

with myView as (
      Select t.tuneId, t.name
      from Tune t
      where <someCondition>
    )
select m.name, mv.name
from Map m left join
     myView mv 
     on m.id = mv.tuneID and
        <conditions on myView>
where <conditions not on myView> ;

If map might have no rows, then the roles are reversed:

with myView as (
      Select t.tuneId, t.name
      from Tune t
      where <someCondition>
    )
select m.name, mv.name
from myView mv left join
     Map m
     on m.id = mv.tuneID and
        <conditions on map>
where <conditions not on map> ;

Upvotes: 1

fuNcti0n
fuNcti0n

Reputation: 189

if you want always retrieve data use FULL OUTER JOIN

with myView as (Select t.tuneId, t.name from Tune t where <someCondition>)

Select m.name, mv.name from Map m 
FULL OUTER JOIN myView mv ON 
m.id=mv.tuneID where <someCondition>

Upvotes: 1

Related Questions