SQL - SELECT Question

[Freid001]

Hi, I'm not sure if this is possible and it might be a bit of a wired question but basically. Is there a away to select a column from a name stored in a row. 

 

So  for example: 

 

I have these to tables: 

 

TABLE 1

| ID | USER | Column Needed |

| 1  | Bob    | i2                        |

| 2  | Jim     | i4                        |

 

TABLE 2 

| i1 | i2 | i3 | i4 | i5|

| 0 |  9 | 1  | 0  | 2 |

 

And I want to select Bob and the column from TABLE 2 that is needed is i2.

 

I then also want to select Jim and the column from TABLE 2 that is needed is i4.

 

I want to do this all in one SQL query. And the column name that they need for each row is stores in TABLE 1 under column needed. 

 

Thanks for any help!!!!

 

 

[kicken]

No, what you want is not possible in a single query. It also sounds like a terrible database design. Why are you wanting to do this? What problem are you trying to solve with this design?

[Freid001]

Yes I know its bad data base design. I originally created it ages ago when I didn't know too much about databases. And now I am kind of stuck with it.

 

I have 2 tables one stores all items available. The other stores how many of each item a user has. But it stores them via column hence the i1,i2, i3 .

Edited January 26, 2014 by Freid001

[Freid001]

I guess is it kind of similar to a count( ) what I want to do. Just not count the data but get it. maybe.

Edited January 26, 2014 by Freid001

[Freid001]

Think I may have to do two queries then and then use some PHP to get what I want. Horrid. If only I could go back and tell my younger self to change it. 

[Barand]

They should be like this

mysql> SELECT * FROM table_1;
+----+------+-----------+
| id | user | needed_id |
+----+------+-----------+
|  1 | Bob  |         2 |
|  2 | Jim  |         4 |
+----+------+-----------+

mysql> SELECT * FROM table_2;
+----+--------------+
| id | needed_value |
+----+--------------+
|  1 |            0 |
|  2 |            9 |
|  3 |            1 |
|  4 |            0 |
|  5 |            2 |
+----+--------------+

However, as you have it now, horrible db design calls for horrible queries

SELECT a.id, a.user, a.column_needed, b.val
FROM table_1 as a
    INNER JOIN 
    (
    SELECT 'i1' as id, i1 as val FROM table_2
    UNION
    SELECT 'i2', i2 FROM table_2
    UNION
    SELECT 'i3', i3 FROM table_2
    UNION
    SELECT 'i4', i4 FROM table_2
    UNION
    SELECT 'i5', i5 FROM table_2
    ) as b ON a.column_needed = b.id

+----+------+---------------+------+
| id | user | column_needed | val  |
+----+------+---------------+------+
|  1 | Bob  | i2            |    9 |
|  2 | Jim  | i4            |    0 |
+----+------+---------------+------+