Can I use CASE statement in a JOIN condition?

by

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.)

More Related Contents:

  1. How to display data in JOIN, and return null value? [closed]
  2. SQL update from one Table to another based on a ID match
  3. How do I perform an IF…THEN in an SQL SELECT?
  4. Difference between JOIN and INNER JOIN
  5. Difference between left join and right join in SQL Server [duplicate]
  6. “CASE” statement within “WHERE” clause in SQL Server 2008
  7. Implement paging (skip / take) functionality with this query
  8. OR is not supported with CASE Statement in SQL Server
  9. Natural join in SQL Server
  10. Case in Select Statement
  11. Joining multiple tables in SQL
  12. SELECT query with CASE condition and SUM()
  13. SQL Inner Join On Null Values
  14. Dynamic order direction
  15. Removing duplicate rows (based on values from multiple columns) from SQL table
  16. How do I obtain a Query Execution Plan in SQL Server?
  17. Remove Identity from a column in a table
  18. efficient way to implement paging
  19. SQL Server – transactions roll back on error?
  20. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  21. Record returned from function has columns concatenated
  22. PostgreSQL JOIN with array type with array elements order, how to implement?
  23. What does sp_reset_connection do?
  24. Replace first occurrence of substring in a string in SQL
  25. How to correctly insert newline in nvarchar [duplicate]
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL – How do I get only the numbers after the decimal?
  29. How to retrieve records for last 30 minutes in MS SQL?
  30. Executing SQL query on multiple databases

Leave a Comment