Problem getting data without temp table

Question

Post reply

Problem getting data without temp table

bodozer

SSC Enthusiast

Points: 196
More actions
November 11, 2004 at 12:38 pm

#66333

I have a table (TFI) that contains the column names of another table (T1). I want to do a SELECT joining these tables and getting the value in the referenced field in T1.
Does anyone know of a way to get the value of the column in another table without building a temporary table?
Example:
T1 - has two columns T1_ID and T1_Col
values are (1,field1,2,field2,3,field3)
F1 - has four columns (F1_ID,field1,field2,field3)
values are (1,'A','B','C',2,'D','E','F')
I want to get the values in F1 by referencing the T1_Col name.
Let me know if this is not clear.

Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 1

Hi,

Are you Saying:

t1_ID	ColumnName
1	Field1
2	Field2
3	Field3

and

f1_ID	Field1	Field2	Field3
1	a	b	c
2	d	e	f
3	x	y	z

If That is the case....What do you want to do with them?

Kindest Regards,

Tal Mcmahon

bodozer SSC Enthusiast Points: 196 More actions · Answer 2

I want to be able to do a SELECT on T1 and get the value of the field in F1 back. Of course I have simplified the problem I am having to the basics.

Hope that makes since and you can help.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Is this the sort of thing?

declare @col varchar(10), @SQLText varchar(255)

select @col = col

from t1

where id = 2

set @SQLText = 'select ' + @col + ' from f1'

exec (@SQLText)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dongadoy SSC Eights! Points: 880 More actions · Answer 4

It's not clear, but maybe you're asking for a simple join?

select T1_ID, T1_COL, field1, field2, field3

from T1 inner join F1 on (T1.T1_ID = F1.F1_ID)

Daniel Stefaniu SSC Enthusiast Points: 123 More actions · Answer 5

/* creating the date (if not available) */

IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1

CREATE TABLE #T1 (T1_ID INT, T1_Col VARCHAR(256))

INSERT INTO #T1 SELECT 1, 'field1'

INSERT INTO #T1 SELECT 2, 'field2'

SELECT * FROM #T1

IF OBJECT_ID('tempdb..#F1') IS NOT NULL DROP TABLE #F1

CREATE TABLE #F1 (F1_ID INT, field1 VARCHAR(256), field2 VARCHAR(256), field3 VARCHAR(256))

INSERT INTO #F1 SELECT 1, 'A','B','C'

INSERT INTO #F1 SELECT 2, 'D','E','F'

INSERT INTO #F1 SELECT 3, 'x','y','z'

SELECT * FROM #F1

/* finished creating the data */

DECLARE @a VARCHAR(8000), @b-2 VARCHAR(8000)

SELECT @a = ' SELECT ', @b-2 = ' FROM #F1'

/* creates the list of columns */

SELECT @a = @a + ISNULL(T1_Col + ',', '')

FROM #T1 cols

/* removes the last ',' */

SELECT @a = REPLACE (@a + ',', ',,', '')

EXEC (@a + @b-2)

Daniel

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 6

Maybe i'm missing something......

Does:

SELECT F1.F1_ID, F1.field1, F1.field2,F1.field3

FROM F1, T1

WHERE T1.Feild1 = F1.Feild1

AND T1.Feild2 = F1.Feild2

AND T1.Feild1 = 'Whatever it is you want'

Not cover what you are looking for?

Steve

We need men who can dream of things that never were.

bodozer SSC Enthusiast Points: 196 More actions · Answer 7

Phil you have the general idea but the id = 2 is too simple. I want to to do this for all records and update a field in one pass. I don't think this can be done. Maybe I simplified my problem too much for the posting because it is a much bigger problem than most people are giving solutions for. I tried to make it a simple as possible. I think I am going to have to either use a temp table or a cursor. Thanks for trying.