'dynamic' SQL join possible?

Question

Hi,

I have a table Action with a (part of the) structure like this:

Action: ActionTypeID, ActionConnectionID ...

ActionTypeID refers to types 1-5 which correspond to different tables (1=Pro, 2=Project etc.) ActionConnectionID is the primaryKey in the corresponding table;

ie. ActionTypeID=1, ActionConnectionID=43 -> would point to Pro.ProID=43 and ActionTypeID=2, ActionConnectionID=233 -> would point to Project.ProjectID=233

Is there a way to 'dynamically join the different tables depending on the value in the ActionTypeID column? ie. for records with the ActionTypeID=1 this would be:

Select Action.* 
From Action Left Join Pro On Action.ActionConnectionID=Pro.ProID

for records with the ActionTypeID=2 this would be:

Select Action.* 
From Action Left Join Project On Action.ActionConnectionID=Project.ProjectID

etc.

If this is not possible to accomplish in one query I will have to loop over all the possible ActionTypes and perform the query and then afterwards join the data in one query again - that would be possible, but doesnt sound like the most efficient way :-)

Answer 1

Something like this should do:

Select Action.*  
From   Action 
       Left Join Pro 
             ON Action.ActionConnectionID=Pro.ProID and ActionTypeID=1
       Left Join Project 
             ON Action.ActionConnectionID=Project.ProjectID and ActionTypeID=2

If that doesn't work for either try using dynamic sql which is a bad solution or properly normalize your data.

Answer 2

Are you just trying to select everything without any filters at all? I always hate when people give answers that are basically "don't do t like that, do it like this instead" but now I'm going to go ahead and do that myself. Have you considered a different schema where you don't have to write this kind of query? I assume that the Pro, Project, etc, tables all have the same schema - can they be combined into one table? Perhaps you don't have control over that and are working with a DB you can't change (been there myself). You should explore using UNION to join up the pieces that you need.

(Select Action.* 
From Action Left Join Pro On Action.ActionConnectionID=Pro.ProID)
UNION
(Select Action.* 
From Action Left Join Project On Action.ActionConnectionID=Project.ProjectID)