I have a table called
MyTable with varchar columns named
SourceID contains a table name and
ReferenceID contains a key value for the source table.
Is it possible in a query to refer to the source table's row assuming I know the name of the key column that matches the value in
SELECT * FROM MyTable as MT INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID
This doesn't work, obviously. But is there a way to do this as opposed to dynamically building the SQL statement using the values of
ReferenceID and then executing the query? More specifically, is there a way to make
MyTable.SourceID in the example above be usable within the JOIN?
I found this article, and it has a trick like follows:
select E.EmployeeName, coalesce(s.store,o.office) as Location from Employees E left outer join Stores S on ... left outer join Offices O on ... where O.Office is not null OR S.Store is not null
The WHERE clause simply helps the outer joins behave like INNER JOINs. This would work well enough if there were a limited number of table options and they all had UNIQUE constraints. This gets me part of the way there but but I really want is to have the table names in the JOINs to be based on a column in
I haven't given up yet. Any and all help is greatly appreciated.