Home How to check for the existence of a value in a column within a Select CASE statement

Questions

How to check for the existence of a value in a column within a Select CASE statement

February 22, 2023

Let’s say I want to get all of the people in a database by name, and have a BIT field to denote whether or not at least one of their jobs is as a plumber. If I have a query like this:

SELECT p.Name,
       CASE
           WHEN EXISTS (select 1 FROM j WHERE j.Name = 'plumber') THEN 1
           ELSE 0
           END IsPlumber
FROM People p INNER JOIN Jobs j
     ON j.personId = p.Id
GROUP BY p.Name

It seems to make sense, but doesn’t work because I get Invalid object name 'j'. I can do a count like this:

...
    COUNT(
        CASE
            WHEN j.Name = 'plumber' THEN 1
            END) > 0
            THEN 1
    ELSE 0
    END IsPlumber
...

But COUNT is less efficient as far as I’m aware, and I just feel like I should be able to go with the top variant somehow. How do I make that work?

>Solution :

If you want to use a join to accomplish this you need to use a LEFT JOIN and the aggregate function, MAX, to calculate it.

SELECT p.Name
    , MAX(CASE
        WHEN j.Name = 'plumber' THEN 1
        ELSE 0
        END) IsPlumber
FROM People p
LEFT JOIN Jobs j ON j.personId = p.Id
GROUP BY p.Name

Alternatively you could put the complete exists query in the CASE expression.

SELECT p.Name
    , CASE
        WHEN EXISTS (
            SELECT 1
            FROM Job j
            WHERE j.PersonId = P.id
            AND j.Name = 'plumber'
        ) THEN 1
        ELSE 0
        END IsPlumber
FROM People p
GROUP BY p.Name