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?
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?
I disagree with the solution suggesting 2 left joins. I think a table-valued function is more appropriate so you don't have all the coalescing and additional joins for each condition you would have.
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:Example code:
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.This is just to add the point that query can be constructed dynamically based on conditions. An example is given below.