CASE is not working in SQL Server

我试图写if..else条件里面的sql我想从公司名表中获取公司名称，如果他们在companyID中的companyID是NULL print“N / A”con someone help me

SELECT [contactID]
      ,[customerID]
      ,(SELECT Label.labelContactType FROM  Label WHERE Label.labelContactTypeID = Customer_Contacts.labelContactTypeID)AS Type
      ,[contactDetails]
      ,[status]
      ,[notes]
      ,CASE WHEN [Customer_Contacts].companyID = NULL THEN 'N/A'
        WHEN [Customer_Contacts].companyID  <> NULL THEN (SELECT [companyName]
        FROM [TaskManagementSystem_DB].[dbo].[Company] WHERE [Company].companyID = [Customer_Contacts].companyID)
        END AS Company

  FROM [TaskManagementSystem_DB].[dbo].[Customer_Contacts]

  WHERE customerID = 24

---

Why not just use a JOIN for this query:

SELECT cc.[contactID]
    ,cc.[customerID]
    ,l.labelContactType AS Type
    ,cc.[contactDetails]
    ,cc.[status]
    ,cc.[notes]
    , COALESCE(cp.[companyName], 'N/A') AS Company
FROM [TaskManagementSystem_DB].[dbo].[Customer_Contacts] cc
LEFT JOIN [TaskManagementSystem_DB].[dbo].[Company] cp
    on cc.companyID = cp.companyID
LEFT JOIN Label l
    on cc.labelContactTypeID = l.labelContactTypeID
WHERE cc.customerID = 24

If you need help with join syntax, here is a great visual explanation of joins

---

尝试使用'IS NULL'而不是'= NULL'，'IS NOT NULL'而不是'<> NULL'。

---

I would really rewrite this using JOIN s: it is more readable, maintainable, and will be more efficient. What you are currently doing is implementing correlated subqueries (two of them; one for the label, one for the company name). Here is your query rewritten with JOIN s and reformatted for readability:

SELECT 
    cust.[contactID]
    ,cust.[customerID]
    ,l.labelContactType AS Type
    ,cust.[contactDetails]
    ,cust.[status]
    ,cust.[notes]
    ,ISNULL(comp.companyName, 'N/A') AS Company
FROM 
    [TaskManagementSystem_DB].[dbo].[Customer_Contacts] cust
LEFT JOIN
    [TaskManagementSystem_DB].[dbo].[Label] l
    ON
    cust.labelContactTypeID = l.labelContactTypeID
LEFT JOIN
    [TaskManagementSystem_DB].[dbo].[Company] comp
    ON
    cust.companyID = comp.companyID
WHERE 
    cust.customerID = 24

I would research the JOIN syntax if you are unfamiliar with it.

链接地址: http://www.djcxy.com/p/86290.html

上一篇: INNER JOIN和LEFT / RIGHT OUTER JOIN的问题

下一篇: CASE在SQL Server中不起作用