SQL Joining tables based on a list of names

Question

Hi,

I'm looking for a way to join a series of SQL tables based on a list of table names. The list of table names can change as the system grows but I need to make sure the join operates on all the tables as they grow.

Each table in the list will have a common column called ActivityID which is a foreign key back to the Activity table which is the source table of the join.

I'd thought about maybe creating a varchar and building the query up before executing. Is this the best way or can the join syntax be created without varchar building?

Answer 1

Standard SQL can't do this. It's usually a bad design anyway. Certain vendors have extensions that allow this like Oracle has EXECUTE IMMEDIATE in anonymous PL/SQL blocks.

You either need to build up the statement externally or use something like a stored procedure to build up a statement and execute it.

Answer 2

I'd place the query in a view. When you add a new table, alter the view to include the new table. That would be much more efficient than dynamic sql.

If you don't control who/when adds a table, something like the following dynamic SQL would work in Sql Server:

declare @query nvarchar(max)
set @query = 'select * from t1 '

select  
    @query = @query + 'left join ' + name + 
        ' on t1.ActivityID = ' + name + '.ActivityID '
from sys.tables
where name like '%Activity%'

exec (@query)

Then again, I wonder how the calling program knows which fields to expect.