CODEWITHSUNDEEP
sqlsql-servert-sqljoininner-join
KristianB
KristianB

Reputation: 1443

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:

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)

Upvotes: 30

Views: 63262

Answers (4)

Andy
Andy

Reputation: 2354

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 
            '[email protected]' -- 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.

Upvotes: 0

Sparky
Sparky

Reputation: 15075

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

Upvotes: 27

Tomalak
Tomalak

Reputation: 338208

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

Upvotes: 3

David
David

Reputation: 16120

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.

Upvotes: 12

Related Questions