Can I use CASE statement in a JOIN condition?

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

SELECT  * FROM    sys.indexes i     JOIN sys.partitions p         ON i.index_id = p.index_id      JOIN sys.allocation_units a         ON CASE            WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1           ELSE 0           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

Instead, you simply JOIN to both tables, and in your SELECT clause, return data from the one that matches:

I suggest you to go through this link Conditional Joins in SQL Server and T-SQL Case Statement in a JOIN ON Clause

e.g.

    SELECT  *FROM    sys.indexes i        JOIN sys.partitions p            ON i.index_id = p.index_id         JOIN sys.allocation_units a            ON a.container_id =            CASE               WHEN a.type IN (1, 3)                   THEN  p.hobt_id                WHEN a.type IN (2)                   THEN p.partition_id               END 

Edit: As per comments.

You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.

Try this:

...JOIN sys.allocation_units a ON   (a.type=2 AND a.container_id = p.partition_id)  OR (a.type IN (1, 3) AND a.container_id = p.hobt_id)