Extracting null fields in SQL Server

I will try to be as detailed as possible. Our corporate controller has asked me for some information regarding our suppliers. Here are the tables:

spp = supplier table, each supplier has one record, there are 5,222 records

ast = supplier account profile, there is a (M, 1) relationship between this table and spp, there are 8,950 records in this table. Each duplicate spp_id has a different atp_id which is a transaction profile.

crt = bank account information, a supplier may or may not have bank account info

xvd = table of checking tables, xvd.xcd_id is the field that holds the checking table id. Checking table 0007 is the table that contains the discount info.

Here is my script:

select spp.spp_id supp_num,
   spp.spp_matchname supp_name,
   case when spp.spp_ddcalculation = 0 
       then 'End of Month' 
       else
       case when spp.spp_ddcalculation = 1 
           then 'Net' 
           else
           case when spp.spp_ddcalculation = 2 
               then 'End of 10, 20, 30' 
               else 
               case when spp.spp_ddcalculation = 3
           开发者_如何学Python        then 'End of 15 or 30'
                   else null
               end 
           end 
       end 
   end calculation1,                                              
   convert(varchar(2), spp.spp_ddduration) + case when spp.spp_ddmd = 0 
                                                 then ' Days' 
                                                 else case when spp.spp_ddmd = 1
                                                     then ' Months'
                                                     else null
                                                 end 
                                             end duration1,
   spp.spp_ddday stop_day1,
   xvd.xvd_desc discount,
   crt.crt_name bank,
   case when ast.ast_ddcalculation = 0 
       then 'End of Month' 
       else
       case when ast.ast_ddcalculation = 1 
           then 'Net' 
           else
           case when ast.ast_ddcalculation = 2 
               then 'End of 10, 20, 30' 
               else case when ast.ast_ddcalculation = 3
                   then 'End of 15 or 30' 
                   else null
               end
           end 
       end 
   end     
   calculation2,
   convert(varchar(2), ast.ast_ddduration) + case when ast.ast_ddmd = 0 
                                                 then ' Days' 
                                                 else case when ast.ast_ddmd = 1
                                                     then ' Months'
                                                     else null
                                                 end 
                                             end                            
   duration2,
   ast.ast_ddday stop_day2

from spp left join ast on spp.spp_id = ast.spp_id
     left join crt on ast.crt_id = crt.crt_id
     inner join xvd on ast.cfd_id = xvd.xcv_id 

where xvd.xcd_id = '0007'
    and xvd.lng_id = 0

order by spp.spp_id

The problem is that there are 371 records in the ast table that have a non null cfd_id which is the field that relates to the discount in checking table 0007. When I run this I get 371 records, but I need all suppliers, even those with null discounts. I know the problem is a combination of my joins and the fact that there is not a null xcv_id in checking table 0007. Can anyone see anything glaring that I have overlooked?

To recap, there are 8,950 records in ast, but only 371 of them have a non null cfd_id. I need to grab all 8,950 records, I can't seem to extract the null discounts. I think I can probably pull everything into a temp table then grab the discounts, but am wondering if there is a way to do this in one select statement.

Thanks

Tony

Edit: The last line of my from statement seems to be the primary issue inner join xvd on ast.cfd_id = xvd.xcv_id

There are no null xcv_id but there are null cfd_id. Is there another way to join those two tables, besides checking for equality?

Forgot to mention, we are on SQL Server 2008 R2.

---

Does this solve the problem ?

FROM   spp
   LEFT JOIN ast
     ON spp.spp_id = ast.spp_id
   LEFT JOIN crt
     ON ast.crt_id = crt.crt_id
   INNER JOIN xvd
     ON xvd.xcv_id = ast.cfd_id
WHERE  xvd.xcd_id = '0007'
   AND xvd.lng_id = 0

---

I think you can just change your inner join to a left join:

from spp left join ast on spp.spp_id = ast.spp_id 
left join crt on ast.crt_id = crt.crt_id  
inner join xvd on ast.cfd_id = xvd.xcv_id 

to

from spp left join ast on spp.spp_id = ast.spp_id 
left join crt on ast.crt_id = crt.crt_id 
left join xvd on ast.cfd_id = xvd.xcv_id 

If you are saying that you want to select records where xvd.xcd_id is 0007 or null then change your where clause to this:

(xvd.xcd_id = '0007' OR xvd.xcd_id is null)

---

This sounds like a perfect use for views. Instead of trying to build one complex query, you could build a series of views that build upon one another filtering the data the way you want it... then apply the final query to the last view.