1

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.

  • Screenshots are discouraged here for the reasons outlined in this link. It should be easy enough to replace the screenshots with formatted text - help us to help you! – Vérace Jun 5 at 19:09
2

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.

| improve this answer | |
  • Thanks much for the feedback - not it all became clear! – query_question Jun 8 at 19:15
  • Glad to help. I appreciate someone looking for understanding even after they have the "answer". – MrPshaw Jun 8 at 19:20
  • SQL study gives tons of food for thought (regarding alternative ways to query something or the rationale behind a result...), the documentation is not always sufficient to fill all the gaps. – query_question Jun 8 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.