CODEWITHSUNDEEP
sqlsql-server-2008
Amy
Amy

Reputation: 1

Need zero value to show in query

My query has many left joins so I apologise for not entering properly to begin with. I do have a time frame that I use but I felt it was not needed originally. I just added it in.

My select statement has about 10-15 columns in my original query. -I did not include them because they are unimportant (just extra data needed).

My FROM statement starts with a table called Cases c.

From there I have many left joins that eventually connects to the inv_items table. This example I only included the left join showing the Cases table and Inv_items table

Declare @start datetime, @end datetime

Select @start = '8-21-2017', @end = '8-21-2018'


select  
  mcl.procedure_name [Procedure],
  ii.INVITM_ITEM_NAME [Item name], 
  count(ii.INVITM_ITEM_NAME) [Count]
from 
   Cases C   
   left join Procedure_done MCL on
     on mcl.case_no = c.case_no
     and mcl.Station_ID = c.Station_ID 
   left join Inv_Items II
     on II.Part_Number = mcl.INV_Part_Number
where 
  and ii.INVITM_ITEM_NAME not like 'null'
group by  
  ii.INVITM_ITEM_NAME
order by
  ii.INVITM_ITEM_NAME asc

My database contains over 10,000+ active inventory items. I need a count for each item even if it has not been used. They are trying to eliminate products not being used.

I apologize if hard to understand. I self taught myself SQL only from using my database I work with. Other than that, it is foreign to me except for when I need something special like showing 0 values. I am thinking my constraints are limited but not sure.

Upvotes: 0

Views: 390

Answers (1)

xQbert
xQbert

Reputation: 35323

Going back to the original post before the edits... Somewhere along the way too much was removed that obfuscated the problem; and not enough included to really show us the problem.

The two fundamental issues I see are

  1. Count needs to come from the right table (MCL) not the left (II)
  2. The and MCL.MCL_Status = '7' in the where negates the left join so you need to move that to the join itself.

These other issues could be contributing in some way.

  1. Where does table SR table alias come from on the last where clause limit?
  2. Where does C alias come from
  3. Where does MCL alias come from (table INV_ITEMS is represented twice?) come from
  4. When using a left join any limiting value on the right side of the left join MUST be on the join itself or the left join becomes an inner join.
  5. 'NULL' as string is different then NULL the absence of data.

So we need to clean up the SR, C, MCL, II table aliases and we need to move and MCL.MCL_Status = '7' to the join instead of the where clause or the left join now behaves like an inner join (it is this line that I believe is eliminating the bronze record). Now I'm not sure if you're using the word 'null' as a string to mean something; but usually this is bad form and we want to eliminate NULL (the absence of data) since dates can't contain null and I'm assuming c.Procedure_Date is a date; we need to change that to handle null appropriately by using the is not null syntax.

Then we need the count to come from MCL as we want the count from the right side table while the complete list of names comes from the left side table. So you only want to count "BRONZE" records that have a status of 7 of which there are 0 right?

THE BELOW IS STILL INCOMPLETE AND WILL NOT WORK UNTIL WE RESOLVE THE TABLE ALIASES.. However it is my hope that the identification of the above gives you sufficient information to resolve the problem. 

SELECT ii.INVITM_ITEM_NAME [Item name]
    , COUNT(MCL.INV_Part_number) [Count] 

FROM Inv_Items II
LEFT JOIN join Inv_Items MCL
  on II.Part_Number = MCL.INV_Part_Number 
 and II.InvItm_Active = '1' 
 and ii.PART_NUMBER_GUID=mcl.PART_NUMBER_GUID
 and MCL.MCL_Status = '7' 
WHERE (c.HIDDEN is null or c.HIDDEN <> 1) 
  and c.Procedure_Date between @start and @end 
  and c.Procedure_Date is not null 
  and ii.INVITM_ITEM_NAME is not null
  and sr.REPOSITORY_ID = (@repository)

GROUP BY ii.INVITM_ITEM_NAME
ORDER BY ii.INVITM_ITEM_NAME asc

The above illustrates why providing a MCVE (Minimally Complete and Verifiable Example) along with expected results is extremely useful. We can't actually solve the problem because there's just too much wrong; all we can do is point to possibilities.

Upvotes: 1

Related Questions