Skip to content
Advertisement

SQL Can I use CASE statement with addition condition in a JOIN condition

I want to use join with case statement and addition condition, for example :

SELECT
    TableA.* 
FROM
    TableA A
INNER JOIN 
    TableB B ON A.ID =  B.TableA_ID 
    CASE
        WHEN @zerocond = 1 
            THEN AND A.OrganizationId != '0'
        WHEN @zerocond = 2
            THEN AND COM.OrganizationId IN(1,2)
    END

But this code results in a syntax error. Can anyone help to explain a little?

Advertisement

Answer

Using Case When then in Join Condition and in Where Clause is not possible in this case What you are trying to achieve is doable with And and OR like below.

SELECT
    TableA.* 
FROM
    TableA A
INNER JOIN 
    TableB B ON A.ID =  B.TableA_ID 
    
Where ((@zerocond = 1 
            AND A.OrganizationId != '0')
        OR (@zerocond = 2
             AND COM.OrganizationId IN(1,2)))
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement