Build inner join statement use column values?
-
i have a table called record_locks that holds an id, tablename and a tablekeycolumn.
what i am trying to achieve is to inner join the above (record_locks) table to the table specified in the tablename column.
is this possible?
-
Sorry, I can't understand...
Would you possibly specify your needing? Can you give an example of the otuput table?
-
yes, sorry - its a bit difficult to explain in words...
select a, b, c from record_locks inner join <<record_locks.table_name>> on record_locks, etc......
the table that the record_locks table should inner join to is what is specified/stored in its column called "table_name"
hope that makes it clearer???
-
Sorry, my brain is very tired today
Which is the column to join? The only possible column is the key_column, isn't it?
In your query, which are 'a, b, c'?
-
on further thinking about it - i dont think the inner join scenario will work actually as the table that record_locks would need to inner join to "could" be different for each record in record_locks...
what i am trying to achieve is:
for simplicity if i have 2 tables: user, companies
and a user is editing one of the records in the users or companies table then an entry in the record_locks table is made, which holds the name of the table (users or companies), the column name for display below (eg surname, companyname) and the id of the record being edited.
i then have a screen which lists all current record locks which shows the table being edited (users or companies) and the person who is currently editing/locked it.
what i also want to show on this list is the name (surname or companyname) of the record in the table that is being edited.
eg
lockedby tablename record being edited
jim users Billy Smart
bill companies The Circus
so it will require some sort of subselect to get "billy smart" from users table and "The circus" from companies table by using the value in the record_locks.table_name column which would contain users for record 1 and companies for record 2 in the record_locks table.
hope this is better explained......
-
HI There,
I'm not sure if this is what you are looking for.
I have not displayed all the columns you are looking for but I am sure you will get the idea:
DECLARE @VvcSQL VARCHAR(MAX)
DECLARE @VinLoop INT
DECLARE @VinLoopMax INT
SELECT
@VinLoop= 1
,@VinLoopMAX = MAX(record_locks_ID)
,@VvcSQL = ''
FROM dbo.record_locks
WHILE (@VinLoop<=@VinLoopMAX)
BEGIN
SELECT @VvcSQL = @VvcSQL + 'SELECT sForename,''' + tablename + ''' as [Table Name] FROM ' + tablename + ' WHERE ' + tablekeycolumn + ' = ' + CAST(ID as VARCHAR)
+ CASE WHEN @VinLoop = @VinLoopMAX THEN '' ELSE '' + CHAR(10) + CHAR(13) + 'UNION' + CHAR(10) + CHAR(13) END
FROM dbo.record_locks
WHERE record_locks_ID = @VinLoop
SET @VinLoop = @VinLoop + 1
END
--PRINT @VinSQL
EXEC (@VvcSQL)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
yes, that has pretty much answered my question thanks...
i was hoping to be clever and somehow use the value in the column as a type of literal, but looping through them first and then retreiving is the best answer..
thanks for your patients....
-
HI Dave,
If you wish to challenge yourself, try and look at the system tables and see if you can do it from there!
I'm not sure if it is possible but if I find some time I'll have a look for you.
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply