Conditional Inner Join
Solution 1
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
Solution 2
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.
Solution 3
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 */
KristianB
My linked in profile: http://dk.linkedin.com/in/kristianebak
Updated on July 09, 2022Comments
-
KristianB almost 2 years
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 tableTimeRegistration
else it should join onDrivingRegistration
.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 theRegType
, like this:WHERE (CASE RegT.Type WHEN 1 THEN TReg.RegistreringsId ELSE DReg.RegistreringsId END = R.Id)