Query Column Name is Value in another table

phil72 · #1 (**permalink**) 11-12-09, 16:14

Hi
i have Table1
Columns Col1 ,col2
Values AA A1
BB A2
CC B3

Table 2
Columns A1, A2, B3, B4 ...
values x y z 1

I want to get result :

AA x
BB y
CC z

Select Tab1.col1, (select Tab1.col2 from Table2 Tab2) from Table1 Tab1

can Tab1.col2 evaluate to return value from Tab2?

Thanks in Advance

n_i · #2 (**permalink**) 11-12-09, 16:54

You could use the CASE expression for that:

Code:

select
  t1.col1,
  case t1.col2 
    when 'A1' then t2.a1
    when 'A2' then t2.a2
    ...
   end
from table1 t1, table2 t2

Make sure the columns in table2 are cast to the same type, if they are not originally of the same type.

phil72 · #3 (**permalink**) 11-12-09, 17:02

Thanks Ivanov

The case state works , but the problem is that my vertical table Table1 has 23 rows and it can grow .

n_i · #4 (**permalink**) 11-12-09, 17:16

Quote:

Originally Posted by phil72

the problem is that my vertical table Table1 has 23 rows and it can grow .

And why is that a problem?

phil72 · #5 (**permalink**) 11-12-09, 21:25

Yes , using case it becomes hardcoded statement, when new rows are added to the table1, then i will have to change query again, as i dont know how many rows will be added and wat values these rows will have.