How to display null values in IN operator for SQL with two conditions in where

I have this query

select * 
from dbo.EventLogs
where EntityID = 60181615 
  and EventTypeID in (1, 2, 3, 4, 5) 
  and NewValue = 'Received'

If 2 and 4 does not exist with NewValue 'Received' it shows this

current results

What I want

1 answer

  • answered 2021-11-29 05:10 Tim Biegeleisen

    Ideally you should maintain somewhere a table containing all possible EventTypeID values. Sans that, we can use a CTE in place along with a left join:

    WITH EventTypes AS (
        SELECT 1 AS ID UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5
    )
    
    SELECT et.ID AS EventTypeId, el.*
    FROM EventTypes et
    LEFT JOIN dbo.EventLogs el
        ON el.EntityID = 60181615 AND
           el.NewValue = 'Received'
    WHERE
        et.ID IN (1,2,3,4,5);
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum