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

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?

CodePudding user response：

Using Case When then in Join Condition and in Where Clause is not possible. 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)))