Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Sample:

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. :)

EDIT:

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.

share|improve this question
    
Sure you shouldn't be asking for data modeling input, rather than help on implementing the design you've come up with? –  eevar Sep 23 '11 at 19:08
    
I'm not sure I follow what you're saying. This isn't a design. I'm trying to find a way to link from table A to unknown table B using a field (or fields) of table A. I'm not convinced yet that it can't be done. –  Jerry Gagnon Sep 23 '11 at 19:28
    
I'm telling you to reconsider your data model. –  eevar Sep 23 '11 at 22:07
    
So far I've only used this mechanic for event recording (a log table) and have referred to the rows programmatically by building the SQL in code. I was hoping for a more "SQL" way of linking the log records to the records they refer to. –  Jerry Gagnon Sep 23 '11 at 23:48
    
XY problem –  eevar Sep 28 '11 at 14:18
show 1 more comment

1 Answer

up vote 2 down vote accepted

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.

share|improve this answer
    
The last two lines of your code are very close to where I ended up. I am hoping someone identifies a way to do it without the use of EXEC(). –  Jerry Gagnon Sep 16 '11 at 18:30
    
SP_EXECUTESQL is the only other way...how else would the query get executed? –  Wil Sep 16 '11 at 19:42
    
From SSMS in a query window or through another application. I'm looking for an "inline" solution if there is one. –  Jerry Gagnon Sep 16 '11 at 20:13
    
1) PRINT the value of @sql, copy it into the query window and execute it. 2) Write a commandline app in your favorite language to execute against the database. 3) Possibly powershell, osql or something similar to execute the statements. –  Wil Sep 16 '11 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.