Conditional Inner Join

sql sql-server tsql join inner-join
KristianB picture KristianB · Sep 1, 2011 · Viewed 47.1k times · Source

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)

Answer

Sparky picture Sparky · Sep 1, 2011

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

Related questions

SQL Left Join first match only

I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read only …

Read More
T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber …

Read More
SQL join against date ranges?

Consider two tables: Transactions, with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency: Date Rate ========= ======= 2/1/2009 40.1 3/1/2009 41.0 4/1/2009 38.5 5/1/2009 42.7 etc. Exchange rates can be entered for arbitrary dates - …

Read More