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     Add error message      Msg 102  Level 15  State 1  Line 6 Incorrect syntax near

User · Answer

Took DonkeyKong s example    The issue is I needed to use a declared variable  This allows for stating your left and right-hand side of what you need to compare  This is for supporting an SSRS report where different fields must be linked based on the selection by the user   The initial case sets the field choice based on the selection and then I can set the field I need to match on for the join   A second case statement could be added for the right-hand side if  the variable is needed to choose from different fields   LEFT OUTER JOIN Dashboard Group Level Matching ON        case          when  Level     lvl1  then  cw Lvl1          when  Level     lvl2  then  cw Lvl2          when  Level     lvl3  then  cw Lvl3        end       Dashboard Group Level Matching Dashboard Level Name

User · Answer

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 a container id    CASE            WHEN a type IN  1  3                 THEN p hobt id             WHEN a type IN  2                 THEN p partition id            ELSE NULL            END

User · Answer

Yes  you can  Here is an example   SELECT a   FROM TableA a LEFT OUTER JOIN TableB j1 ON   CASE WHEN LEN COALESCE a NoBatiment         3                                  THEN RTRIM a NoBatiment     0                                   ELSE a NoBatiment END     j1 ColumnName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here I have compared the difference in two different result sets   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  lt  gt  compare Value THEN 1 END   1

[sql] Can I use CASE statement in a JOIN condition?

Examples related to sql

Examples related to sql-server

Examples related to join

Examples related to case