Conditional Inner Join

Question

I want to be able to inner join two tables based on the result of an expression.

What I've been trying so far:

INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg
ON
RReg.RegistreringsId = R.Id

RegT is a join I made just before this join:

INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id

This SQL-script does not work.

So all in all, if the Type is 1, then it should join on the table TimeRegistration else it should join on DrivingRegistration.

Solution:

In my select statement I performed the following joins:

INNER JOIN  RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
LEFT OUTER JOIN TimeRegistration AS TReg ON TReg.RegistreringsId = R.Id AND RegT.Type = 1
LEFT OUTER JOIN DrivingRegistration AS DReg ON DReg.RegistreringsId = R.Id AND RegT.Type <>1

Then I edited my where-clause to output the correct, depending on the RegType, like this:

WHERE (CASE RegT.Type WHEN 1 THEN TReg.RegistreringsId ELSE DReg.RegistreringsId END = R.Id)

You can't do it that like! `CASE` cannot be used to dynamically swap in and out random parts of the query. Do these tables have the same structures? — Martin Smith, Sep 01 '11 at 09:46

score 25 · Accepted Answer · edited Sep 01 '11 at 11:21

25

Try putting both tables in the query using LEFT JOIN's

LEFT JOIN TimeRegistration TR ON r.rid = TR.Id AND RegT.type =1 
LEFT JOIN DrivingRegistration DR ON r.rid = DR.Id AND RegT.type <>1

Now, in you select clause, use

CASE RegType.Type WHEN 1 THEN TR.SomeField ELSE DR.someField END as SomeField

The other option is to use dynamic SQL

edited Sep 01 '11 at 11:21

Andriy M

76,112
17
94
154

answered Sep 01 '11 at 09:51

Sparky

14,967
2
31
45

Awesome, both Sparky and David's suggestions helped! Will edit my question to what I have done to make it work. – KristianB Sep 01 '11 at 10:05

score 11 · Answer 2 · answered Sep 01 '11 at 09:48

You probably need to perform two left joins, one onto TimeRegistration and one onto DrivingRegistration, and return the fields you want from the appropriate join table something like this:

LEFT JOIN TimeRegistration ON TimeRegistration.RegistreringsId = R.Id
LEFT JOIN DrivingRegistration ON DrivingRegistration.RegistreringsId = R.Id

and you select statement would be something like this:

SELECT CASE WHEN RegT.Type = 1 THEN TimeRegistration.Foo ELSE DrivingRegistration.Bar END

I like what you're trying to do, but I don't think SQL is that clever.

score 2 · Answer 3 · answered Sep 01 '11 at 09:52

2

SELECT
  R.foo, tr.bar
FROM
  SomeTable AS R
  INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id 
                                          AND RegT1.Type = 1
  INNER JOIN TimeRegistration AS tr    ON /* whatever */

UNION 

SELECT
  R.foo, dr.bar
FROM
  SomeTable AS R
  INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id 
                                          AND RegT1.Type = 2
  INNER JOIN DrivingRegistration AS dr ON /* whatever */

answered Sep 01 '11 at 09:52

Tomalak

332,285
67
532
628

1

+1 although will probably need to be `UNION ALL`. If the tables are Union compatible these could be in a CTE with a `type` column to avoid repeating the `JOIN` logic though not sure what the execution plans would look like. – Martin Smith Sep 01 '11 at 10:08
1

@Martin: With the right indexes, the two joins should be plenty fast. I assumed since the two target tables are different, a UNION ALL would not be necessary. – Tomalak Sep 01 '11 at 10:59

score 0 · Answer 4 · answered Oct 10 '18 at 18:02

So I had a scenario where there were three email columns in one table (don't ask why) and any of them could be null (or empty). In this example code I will just deal with a case where it is null.

I had to join it to another table by any of the emails to retrieve the users firstname.

Here is what worked

select  

m.email1,
m.email2,
m.email3, 
m2.firstName

from MyTable m

left join MyOtherTable m2 on m2.Email = 
case when m.email1 is null then 

    case when m.email2 is null then 

        case when m.email3 null then 
            'nonexistent@mydomain.com' -- i stopped here
        else m.email3 end

    else wm.email2 end

else m.email1 end

Obviously you would include further conditions like

case when m.email1 is null or m.email1 = '' then ...

To cover for empty values.

Conditional Inner Join

4 Answers4

Linked