CODEWITHSUNDEEP
sqlt-sql
dMilan
dMilan

Reputation: 257

Join with case statement in tsql

I have table:

CREATE TABLE MyTable (
    RootId int,
    Direction bit,
   ....
);

Now, I must write select from this table and join some tables to it. Joined tables depends on Direction parameter. How to join MyTable3 like here:

 select 
  Root,
  Direction,
  Type
 from MyTable
 join MyTable1 on 
   MyTable1.Id = RootId
 join MyTable2 on 
   MyTable2.Id = RootId

 join MyTable3 on 
   ...
   case select when Direction = 1
     MyTable3.TypeId = MyTable1.TypeId
   else
     MyTable3.TypeId = MyTable2.TypeId

Upvotes: 0

Views: 447

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

For performance reasons, you may want to be using two left joins, like this:

select Root,Direction,
      coalesce(m3_1.Type, m3_2.Type) as type
from MyTable join
     MyTable1 
     on MyTable1.Id = MyTable.RootId join
     MyTable2
     on MyTable2.Id = MyTable.RootId left join
     MyTable3 m3_1
     on m3_1.Direction = 1 and
        m3_1.TypeId = MyTable1.TypeId left join
     MyTable3 m3_0
     on m3_2.Direction = 1 and
        me_2.TypeId = MyTable2.TypeId;

The use of or or case (or really anything other than and in an on clause) can have a big impact on performance.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

The predicate of a CASE expression (i.e what the CASE expression generates) cannot be an equality condition, but rather it has to be a value. You may write the final join condition as follows:

INNER JOIN MyTable3 t3
    ON (Direction = 1 AND t3.TypeId = t1.TypeId) OR
       (Direction <> 1 AND t3.TypeId = t2.TypeId)

Here is the full query:

SELECT 
    Root,
    Direction,
    Type
FROM MyTable t
INNER JOIN MyTable1 t1
    ON t1.Id = t.RootId
INNER JOIN MyTable2 t2
    ON t2.Id = t.RootId
INNER JOIN MyTable3 t3
    ON (Direction = 1 AND t3.TypeId = t1.TypeId) OR
       (Direction <> 1 AND t3.TypeId = t2.TypeId);

Upvotes: 4

Related Questions