conditional ON (join condition) in sql using different columns

Question

I need to use conditional ON clause, with the need to place a different column condition in case of if true and in case of if false Here is the example

select a.* b.*
 from tblA a inner join tblB b
 on a.somefield = b.someOtherField
 AND 
 /*THIS IS WHERE THE PROBLEM STARTS, I am using pseudo code, and need help with the actual code
 (
   if substring(a.field3, 1,3) <>'' - I want to use substring(a.field3, 1,3)=b.field2
   else, I want to use say b.field5='Y'
 )
 */

So, effectively in case of IF evaluating to true, I want a select of this kind:

select a.* b.*
 from tblA a inner join tblB b
 on a.somefield = b.someOtherField
 **AND substring(a.field3, 1,3)=b.field2**

And if condition is false, I want the effective join clause to be

    select a.* b.*
 from tblA a inner join tblB b
 on a.somefield = b.someOtherField
 **AND b.field5='Y'**

Using SQL Server

Thanks

Gordon Linoff · Accepted Answer · 2014-09-08 22:43:49Z

1

Just use boolean logic:

select a.* b.*
from tblA a inner join
     tblB b
     on a.somefield = b.someOtherField AND
        ((substring(a.field3, 1, 3) <> '' and substring(a.field3, 1,3) = b.field2) or
         ( (substring(a.field3, 1, 3) = '' or a.field3 is null) and b.field5 = 'Y'
        );

For clarity, I used substring() in the second clause. It can equally be written as:

         (a.field3 = '' and b.field5 = 'Y')

because if the substring is '', then so is the original string.

answered Sep 8, 2014 at 22:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

conditional ON (join condition) in sql using different columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related