CODEWITHSUNDEEP
sqlreporting-servicesssrs-2012
lostcoder
lostcoder

Reputation: 11

Display blank values

I have a little complex situation.

I have two tables,

Table 1: Loss Reasons This table collects all Scrap Transactions against Parent and Child Containers:

Container Reason Scrap Qty
123-A Bent 5
123-C Scratch 1
123-D Bent 3
123-C Flash 1
123-A Bent 1

Table 2: Containers The purpose of table shows parent containers and their child containers and container qty. P means Parent and C means Child

Container Container-Level Qty
123 P 100
123-A C 20
123-B C 10
123-C C 20
123-D C 20
123-E C 10

I created a query as follows: `

select 
C.container,
lr.container,
sum(case when lr.Reason ='Bent' then lr.scrap_qty) as Bent
,sum(case when lr.Reason ='Flash' then lr.scrap_qty) as Flash
,sum(case when lr.Reason ='Scratch' then lr.scrap_qty) as Scratch
from 
loss_reasons lr
join 
container c on c.container=lr.container
group by C.container ,lr.container `

Once the query is ran, I get the following data output:

Container Bent Flash Scratch
123-A 6 0 0
123-C 0 1 1
123-D 3 0 0

What I would like to do is get the following table instead of the one above, in which even if there is no Scrap transaction created against a Container - regardless of level, the report should still display the container with values of 0 (Please see bold values):

Container Bent Flash Scratch
123 0 0 0
123-A 6 0 0
123-B 0 0 0
123-C 0 1 1
123-D 3 0 0
123-E 0 0 0

Would this be possible? I would appreciate your help in this matter.

Upvotes: 0

Views: 42

Answers (1)

iamdave
iamdave

Reputation: 12243

An inner join only returns records where there is a match on the join criteria. For your requirement you need an outer join which includes non-matching records. Which non-matching records get returned depends on the join type you use, either left, right or full.

In your case, for personal reasons of understandability and ease of future development and maintenance, rather than use a right outer join as recommended by Alan in the comments I would rearrange the tables within the from clause and use a left outer join as follows:

select c.container
      ,lr.container
      ,sum(case when lr.Reason ='Bent' then lr.scrap_qty else 0 end) as Bent
      ,sum(case when lr.Reason ='Flash' then lr.scrap_qty else 0 end) as Flash
      ,sum(case when lr.Reason ='Scratch' then lr.scrap_qty else 0 end) as Scratch
from container c
    left outer join loss_reasons lr
        on c.container = lr.container
group by c.container
        ,lr.container;

In this query, the table on the 'left' of the join (container) will return records regardless of whether a match is found or not in the table on the 'right' of the join (loss_reasons). I have rearranged the tables so that you are starting with your 'base' table that holds all the containers, from which you can then join into the rest of your data.

Personally, starting with 'things' and then joining onto 'events' in this manner makes more sense to me semantically and logically compared with starting from the 'events' themselves.

If the left and right identifiers don't make sense, just think of the join clause as a single line of code referencing only the tables being joined, in the order that they are included in the overall script, with a left and right end to it:

[Left Table] left outer join [Right Table]

so in the statement above, this would be:

container left outer join loss_reasons

Upvotes: 1

Related Questions