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
Ideally you should maintain somewhere a table containing all possible
EventTypeIDvalues. 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);