I have a table (MyTable) with varchar columns named SourceID and ReferenceID where 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 record assuming I know the name of the key field that matches the value in RefrenceID?
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 SourceID and ReferenceID and then executing the query? More specifically, is there a way to make MT.SourceID in the example above be usable within the JOIN?
Any and all help is greatly appreciated. :)
I found this article: http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx
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 keys. 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 field in Employees.
I haven't given up yet.