CODEWITHSUNDEEP
sqlcasecase-when
Linda Stenn
Linda Stenn

Reputation: 31

Thought process of CASE syntax in SQL

I have the following code (I'm using northwind database) which give me the result shown in this picture

SELECT a.FirstName, a.TitleOfCourtesy, a.Notes, c.CustomerYear
FROM employees a
    INNER JOIN orders b
    ON a.EmployeeID = b.EmployeeID
    LEFT JOIN customers c
    ON b.CustomerID = c.CustomerID
WHERE a.TitleOfCourtesy = 'Ms.';

The code above combines three tables and selecting only rows with a.TitleOfCourtesy = 'Ms.'

From the result I want to take the rows that has %1992% in the column a.Notes and only show rows that has the c.CustomerYear equal to 1992. So in the picture earlier I should only get the second top row visible.

I thought I could use the CASE + WHEN syntax to create a kind of IF statement but I only get invalid use of syntax at the CASE line. Should the CASE part be moved after the WHERE part or do I use it completely wrong?

SELECT a.FirstName, a.TitleOfCourtesy, a.Notes, c.CustomerYear

CASE 
    WHEN a.Notes LIKE '%1992%' THEN c.CustomerYear LIKE '1992' 
    ELSE NULL END 

FROM employees a
    INNER JOIN orders b
    ON a.EmployeeID = b.EmployeeID
    LEFT JOIN customers c
    ON b.CustomerID = c.CustomerID
WHERE a.TitleOfCourtesy = 'Ms.';

How should I think to achieve my desired result?

Upvotes: 1

Views: 31

Answers (1)

MatBailie
MatBailie

Reputation: 86765

You just need to expand the WHERE clause...

SELECT a.FirstName, a.TitleOfCourtesy, a.Notes, c.CustomerYear
FROM employees a
    INNER JOIN orders b
    ON a.EmployeeID = b.EmployeeID
    LEFT JOIN customers c
    ON b.CustomerID = c.CustomerID
WHERE a.TitleOfCourtesy = 'Ms.'
  AND a.Notes LIKE '%1992%'
  AND c.CustomerYear LIKE '1992';

I also only see one matching row in your image, not two. So I may have misunderstood what you want to achieve... Maybe you want this to get two rows?

SELECT a.FirstName, a.TitleOfCourtesy, a.Notes, c.CustomerYear
FROM employees a
    INNER JOIN orders b
    ON a.EmployeeID = b.EmployeeID
    LEFT JOIN customers c
    ON b.CustomerID = c.CustomerID
WHERE a.TitleOfCourtesy = 'Ms.'
  AND a.Notes LIKE '%1992%'
  AND (c.CustomerYear LIKE '1992' OR c.CustomerYear IS NULL)

Finally, you don't need LIKE '1992' you can just use = '1992', and if the value is a integer just use = 1992...

Upvotes: 1

Related Questions