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

Question

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)

That doesn't work. Since Map has no data in it, m.id=mv.tuneID join fails and that's why it doesn't return the result. But if myView has no data and Map has data the result displayed with OUTER Joins. — Vimal, Jan 24 '19 at 19:04
Sounds like you MIGHT want to use some form of a union rather than a join? This link is similar: https://stackoverflow.com/questions/20458145/sql-check-if-data-exists-on-either-table — Steve-o169, Jan 24 '19 at 19:15
Probably your WHERE clause is turning your outer join into an inner join. Can't say since you don't show us what it is. https://stackoverflow.com/questions/27607144/sql-left-outer-join-with-where-clause — Tab Alleman, Jan 24 '19 at 19:16
@TabAlleman Please see the edited version. I've mentioned the actual scenario. — Vimal, Jan 25 '19 at 03:41
Is time you show us some sample data current and expected output. We are wasting our time trying to guest what you need — Juan Carlos Oropeza, Jan 25 '19 at 08:44
@Vimal based on your edit, Juan Carlos' answer should solve your problem. — Tab Alleman, Jan 25 '19 at 14:20
@JuanCarlosOropeza Please see the edited version above, hope it clarifies. — Vimal, Jan 25 '19 at 18:09
You only post map data, where is the view data? Also you said the problem is when map doesnt have data. but here map have data still isnt clear if reproduce the problem — Juan Carlos Oropeza, Jan 25 '19 at 18:10
@JuanCarlosOropeza Please see the query carefully, the view is created itself from the Map table. The query is broken into two parts to reduce the data for two different dates. — Vimal, Jan 25 '19 at 18:12
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Try create a sample in http://rextester.com — Juan Carlos Oropeza, Jan 25 '19 at 18:12

score 1 · Answer 1 · answered Jan 24 '19 at 18:56

1

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>

answered Jan 24 '19 at 18:56

fuNcti0n

177
1
11

That doesn't work. Since Map has no data in it, m.id=mv.tuneID join fails and that's why it doesn't return the result. – Vimal Jan 24 '19 at 19:02
2

@Vimal - Please make sure that your *someCondition* does not turn the OUTER JOIN into INNER, since NULL is not equal to anything, including another NULL. – PM 77-1 Jan 24 '19 at 19:08

Juan Carlos Oropeza · Accepted Answer · 2019-01-25T18:52:14.600

1

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;

edited Jan 25 '19 at 18:52

answered Jan 24 '19 at 19:14

Juan Carlos Oropeza

47,252
12
78
118

The problem with the first one is if myView has some rows which Map doesn't, it will not show them as well. I need the rows in either case. e.g. myView has 3 records, Map has 2 records, I need total 5 records and vice versa. – Vimal Jan 25 '19 at 03:02
Check the demo Here you can see the difference Now need to understand what you have on the sum. Because in your example you said A need substract 300, but on your data A doesnt have any rows for that date. Can you elaborate what you want there? – Juan Carlos Oropeza Jan 25 '19 at 18:47
Ohh nevermind I sort the data and found the 300. Let me add the other part – Juan Carlos Oropeza Jan 25 '19 at 18:49
Done check the demo. – Juan Carlos Oropeza Jan 25 '19 at 18:52

score 1 · Answer 3 · answered Jan 24 '19 at 19:49

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> ;

I need this two separate piece of code into one single query — Vimal, Jan 25 '19 at 02:59

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

3 Answers3