Menu
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) 
299
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
The primary difference between an inner and natural join is that inner joins have an explicit join condition, whereas the natural join's conditions are formed by matching all pairs of columns in the tables that have the same name and compatible data types, making natural joins equi-joins because join condition are ...
Always put the join conditions in the ON clause if you are doing an INNER JOIN . So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause. If you are doing a LEFT JOIN , add any WHERE conditions to the ON clause for the table in the right side of the join.
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
105
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
