Can I use CASE statement in a JOIN condition?

Question

The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together I would write something similar to this:

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)
                   THEN a.container_id = p.hobt_id 
               WHEN a.type IN (2)
                   THEN a.container_id = p.partition_id
               END 

But the upper code gives a syntax error. I guess that's because of the CASE statement. Can anyone help to explain a little?

Thanks!

---

Add error message:

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.

Answer 1

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)

Answer 2

This seems nice

https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition

FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom =  DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable

Answer 3

I think you need two case statements:

SELECT  *
FROM    sys.indexes i
    JOIN sys.partitions p
        ON i.index_id = p.index_id 
    JOIN sys.allocation_units a
        ON 
        -- left side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id
               WHEN a.type IN (2)
                   THEN a.container_id
            END 
        = 
        -- right side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN p.hobt_id
               WHEN a.type IN (2)
                   THEN p.partition_id
            END             

This is because:
- the CASE statement returns a single value at the END
- the ON statement compares two values
- your CASE statement was doing the comparison inside of the CASE statement. I would guess that if you put your CASE statement in your SELECT you would get a boolean '1' or '0' indicating whether the CASE statement evaluated to True or False

Answer 4

Here I have compared the difference in two different result sets. Hope this might be helpful.

SELECT main.ColumnName, compare.Value PreviousValue,  main.Value CurrentValue
FROM 
(
    SELECT 'Name' AS ColumnName, 'John' as Value UNION ALL
    SELECT 'UserName' AS ColumnName, 'jh001' as Value UNION ALL
    SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
    SELECT 'Phone' AS ColumnName, NULL as Value UNION ALL
    SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
    SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
    SELECT 'IsActive' AS ColumnName, '1' as Value
) main
INNER JOIN
(
    SELECT 'Name' AS ColumnName, 'Rahul' as Value UNION ALL
    SELECT 'UserName' AS ColumnName, 'rh001' as Value UNION ALL
    SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
    SELECT 'Phone' AS ColumnName, '01722112233' as Value UNION ALL
    SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
    SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
    SELECT 'IsActive' AS ColumnName, '1' as Value
) compare
ON main.ColumnName = compare.ColumnName AND
CASE 
    WHEN main.Value IS NULL AND compare.Value IS NULL THEN 0
    WHEN main.Value IS NULL AND compare.Value IS NOT NULL THEN 1
    WHEN main.Value IS NOT NULL AND compare.Value IS NULL THEN 1
    WHEN main.Value <> compare.Value THEN 1
END = 1 

Answer 5

I took your example and edited it:

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)
               THEN p.hobt_id 
           WHEN a.type IN (2)
               THEN p.partition_id
           ELSE NULL
           END = a.container_id

Answer 6

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.