如何使用join检索空值 [英] how to retrieve null values with join

问题描述

我使用以下查询来检索值..

  SELECT  VoucherTypes.specification，VoucherTypes.types，VoucherHead.VoucherDate，VoucherHead.VoucherNo，Accounts.AccountNo，Accounts.AccountName，
 CostAccounts.CostAccountName，VoucherDetail.VoucherDetail，VoucherDetail.Debit，VoucherDetail.Credit，VoucherHead.VoucherID， Accounts.AccountID 
  FROM  VoucherHead  INNER   JOIN  
 VoucherDetail  ON  VoucherHead.VoucherID = VoucherDetail.VoucherID  INNER   JOIN  
 VoucherTypes  ON  VoucherHead.VoucherType = VoucherTypes.vtypeid  INNER  加入 
帐户 ON  VoucherDetail.Accountid = Accounts.AccountID 已满  OUTER   JOIN  
 CostAccounts  ON  VoucherDetail.costaccount = CostAccounts.ID 
  WHERE （VoucherTypes.types =  @ vtypes  或 
  @ vtypes  = '  0'） AND （VoucherDetail.VoucherDetail  LIKE  < span class =code-sdkkeyword> @ voucherdetail   OR  
  @ voucherdetail  = '  0'） AND （VoucherDetail.Debit =  @ debit  或 
  @ debit  =  0 。 0001 ） AND （VoucherDetail.Credit =  @信用 或 
  @ credit  =  0 。 0001 ） AND （VoucherHead.VoucherDate  BETWEEN   @ date1   AND   @ date2 ） AND （VoucherHead.Branchno =  @ branchid ） AND  
 （CostAccounts.CostAccountName  LIKE   @ coststr  或 
 CostAccounts.CostAccountNo  LIKE   @ coststr  或 
 VoucherDetail.costaccount  IS   NULL ） AND （Accounts.AccountName  LIKE   @ str   OR  
 Accounts.AccountNo  LIKE   @ str ）

但是这个查询没有显示voucherdetail.costaccount为空的任何行...请帮我解决这个问题...

解决方案

嗨Nijish a，



很明显，结果不会返回NULL值，因为SQL在使用等运算符（=）进行比较时不理解NULL值。



你可以在加入比较时尝试ISNULL吗？



类似的东西

 CostAccounts  ON  Isnull（VoucherDetail.costaccount， 0  ）= Isnull（CostAccounts.ID， 0 ）

请让我知道你是否有任何疑虑或疑问，或者我错过了什么。





谢谢

Advay Pandya

I have used following query for retrieving values..

SELECT        VoucherTypes.specification, VoucherTypes.types, VoucherHead.VoucherDate, VoucherHead.VoucherNo, Accounts.AccountNo, Accounts.AccountName, 
                         CostAccounts.CostAccountName, VoucherDetail.VoucherDetail, VoucherDetail.Debit, VoucherDetail.Credit, VoucherHead.VoucherID, Accounts.AccountID
FROM            VoucherHead INNER JOIN
                         VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID INNER JOIN
                         VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN
                         Accounts ON VoucherDetail.Accountid = Accounts.AccountID FULL OUTER JOIN
                         CostAccounts ON VoucherDetail.costaccount = CostAccounts.ID
WHERE        (VoucherTypes.types = @vtypes OR
                         @vtypes = '0') AND (VoucherDetail.VoucherDetail LIKE @voucherdetail OR
                         @voucherdetail = '0') AND (VoucherDetail.Debit = @debit OR
                         @debit = 0.0001) AND (VoucherDetail.Credit = @credit OR
                         @credit = 0.0001) AND (VoucherHead.VoucherDate BETWEEN @date1 AND @date2) AND (VoucherHead.Branchno = @branchid) AND 
                         (CostAccounts.CostAccountName LIKE @coststr OR
                         CostAccounts.CostAccountNo LIKE @coststr OR
                         VoucherDetail.costaccount IS NULL) AND (Accounts.AccountName LIKE @str OR
                         Accounts.AccountNo LIKE @str)

But This query does not show any row in which voucherdetail.costaccount is null...pls help me to solve this...

解决方案

Hi Nijisha,

It's obvious that the result will not return with NULL values because SQL does not understand NULL value while comparing using equal operator (=).

Can you please try ISNULL while comparing in join ?

Something Like

CostAccounts ON Isnull(VoucherDetail.costaccount,0) = Isnull(CostAccounts.ID,0)

Please let me know if you have any concern or query or if I am missing something.


Thanks
Advay Pandya

这篇关于如何使用join检索空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！