Muj Muj - 3 months ago 12
Vb.net Question

Use IF to identify where inner join should i used

I have a stored procedure goes like this

Select Name, Identifier, ReferenceId, ReferenceAccountId
FROM Test


And Before i start the Inner join i Have a field named Identifier,
If This is 1 then i will use the inner join ref_reference1 else i will use the other one.

INNER JOIN Ref_Reference1 ON Ref_Reference1.ReferenceId = Test.ReferenceId
INNER JOIN Ref_Reference2 ON Ref_Reference2.ReferenceId = Test.ReferenceId


can some one know how to solve this trick? or is it possible? thanks in advance

Answer

You don't. You cannot change the "shape" of the query without using dynamic SQL, and you should generally avoid that where possible.

Instead, you can do two left joins:

LEFT JOIN Ref_Reference1 ON
     Ref_Reference1.ReferenceId = Test.ReferenceId
     AND Test.Identifier = 1
LEFT JOIN Ref_Reference2 ON
     Ref_Reference2.ReferenceId = Test.ReferenceId
     AND Test.Identifier <> 1

You'll notice that you now also have a strong benefit - you can reference these tables elsewhere in your query (how were you planning to deal with that in your proposal?)