Conditional JOIN Statement SQL Server

Question

Is it possible to do the following:

IF [a] = 1234 THEN JOIN ON TableA  ELSE JOIN ON TableB 

If so, what is the correct syntax?

Answer 1

I think what you are asking for will work by joining the Initial table to both Option_A and Option_B using LEFT JOIN, which will produce something like this:

Initial LEFT JOIN Option_A LEFT JOIN NULL OR Initial LEFT JOIN NULL LEFT JOIN Option_B 

Example code:

SELECT i.*, COALESCE(a.id, b.id) as Option_Id, COALESCE(a.name, b.name) as Option_Name FROM Initial_Table i LEFT JOIN Option_A_Table a ON a.initial_id = i.id AND i.special_value = 1234 LEFT JOIN Option_B_Table b ON b.initial_id = i.id AND i.special_value <> 1234 

Once you have done this, you 'ignore' the set of NULLS. The additional trick here is in the SELECT line, where you need to decide what to do with the NULL fields. If the Option_A and Option_B tables are similar, then you can use the COALESCE function to return the first NON NULL value (as per the example).

The other option is that you will simply have to list the Option_A fields and the Option_B fields, and let whatever is using the ResultSet to handle determining which fields to use.