Conditional Inner Join

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:

INN开发者_如何学JAVAER 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)

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

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.

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 */

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.