SQL Server : join 2 tables yielding required output-CodePudding

I am trying to join 2 tables TBL_A and TBL_B to get the desired output with color filter.

It is working perfectly when the filtered Color is present in TBL_B.

But it doesn't return the required output when the filtered color value is not in TBL_B

Here is the code I am using at the moment:

DECLARE 
    @START_DATE DATETIME,
    @END_DATE DATETIME, 
    @COLOR VARCHAR(10)

SET @START_DATE = '2021-12-07' 
SET @END_DATE = '2021-12-08' 
SET @COLOR = 'Red'  -- can be Blue or Green based on requirement

SELECT      TBL_A.ID_CLM,
            TBL_A.DAY_CLM,
            TBL_A.DT_CLM,   
            TBL_B.CLR_CLM
FROM        TBL_A
LEFT JOIN   TBL_B 
ON          TBL_B.DT_CLM = TBL_A.DT_CLM
WHERE       TBL_A.DT_CLM BETWEEN @START_DATE AND @END_DATE 
            AND
            (TBL_B.CLR_CLM IS NULL OR TBL_B.CLR_CLM IS NOT NULL)

Graphical representation of what I actually need:

Image with different outputs and required one

CodePudding user response：

You are cancelling your OUTER join by thea fact that you filter on B table... Just remove the filter a set it into the join predicate like this :

SELECT      ...
FROM        TBL_A
            LEFT OUTER JOIN TBL_B 
                 ON TBL_B.DT_CLM = TBL_A.DT_CLM
                    AND (TBL_B.CLR_CLM IS NULL OR TBL_B.CLR_CLM IS NOT NULL)
WHERE       TBL_A.DT_CLM BETWEEN @START_DATE AND @END_DATE