Muj Muj - 2 months ago 8
Vb.net Question

Use IF to identify what inner join should i used

I have a stored procedure 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


How can I solve this?

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?)