T-sql – CASE in WHERE clause – retuns a single-row result instead of 4 rows

casejoin;t-sqlwhere

Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear – I need to show all projects that are related to EmployeeID = 24, if the project has started during or after year 2005, then the ProjectName field should be 'NULL', otherwise the ProjectName should show the actual value.

Once I solve it this way, I receive just a single result in MS SQL Management Studio


SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24 
        AND Projects.[Name] = CASE
            WHEN Projects.StartDate = '2005' THEN NULL
            WHEN Projects.StartDate > '2005' THEN NULL
            ELSE Projects.[Name]
            END

enter image description here

If I run the above code partially:

SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24

there are 4 results as per the attached screenshot:

enter image description here

This solution is suggested and gives the full mark:

SELECT E.EmployeeID,FirstName,
  CASE 
   WHEN P.StartDate > '01/01/2005' THEN NULL
   ELSE P.NAME
  END 
  FROM Employees AS E
INNER JOIN EmployeesProjects AS EP ON EP.EmployeeID = E.EmployeeID
INNER JOIN Projects AS P ON EP.ProjectID = P.ProjectID
WHERE E.EmployeeID = 24

However, I want to ask why my solution shows just a single row, what is my mistake in the way query sequence? Is not it possible to retrieve the same by first doing the joins and then using a WHERE clause which includes a CASE?

Appreciate your feedback!

p.s. in case you need the script (schemas, insert), just let me know and I can add them as well.

Best Answer

The WHERE clause is used to filter data. You aren't trying to filter out the records, instead you want to include them but alter their displayed value based on their actual value. This is why the CASE statement in the SELECT statement gives you the desired results.

Specifically, the reason your query omits 3 results is not because the CASE statement isn't working, but rather that the first 2 WHEN statements will likely always evaluate false. Effectively, when Projects.StartDate >= '2005/01/01' then Projects.[Name] would have to equal NULL for the records to be selected. If employee 24 had any projects started on or after 01/01/2005 and a NULL project name, those records would show up.

To break it down even further, for each record in your query, it will do the following.

  1. Checks if Projects.StartDate = '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
  2. If the check in step 1 evaluates false, then check if Projects.StartDate > '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
  3. If the checks in both step 1 and step 2 evaluate false, then does Projects.[Name] = Projects.[Name] then return record. Hint: it always will.

Because 3 of the records associated with employee 24 were on or after '2005-01-01 00:00:00.000' and did not have NULL project names, they were excluded from the results.

Related Question