Dynamically choosing a column in MySQL

Question

I know how to pipe one MySQL query into another:

SELECT user_name FROM users WHERE user_id=( SELECT user_id FROM entries WHERE header="foo" );

Out of pure intellectual curiosity, how I dynamically choose a column or a table?

Ex:

SELECT ( 
    SELECT column_name FROM column_names WHERE id = 1 
) FROM ( 
    SELECT table_name FROM table_names WHERE id = 1 
);

Answer 1

I'm pretty sure this is impossible with a regular query or view.

Answer 2

You can do it by querying the information_schema.columns table.

Do this and check the results. I'm not sure what you're trying to do but that table contains anything related to your columns:

SELECT * FROM information_schema.`COLUMNS` C;

BTW, I don't know any way of doing this in a single query. You should get the columns information and then create a new query in your coding language, whatever that is.

Answer 3

Use a prepared statement:

mysql> SET @sql = CONCAT("SELECT ", (SELECT "NOW()"));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sql;
+--------------+
| @sql         |
+--------------+
| SELECT NOW() | 
+--------------+
1 row in set (0.00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+---------------------+
| NOW()               |
+---------------------+
| 2009-04-06 23:08:31 | 
+---------------------+
1 row in set (0.00 sec)

Answer 4

In answer to your first question, you should learn about how to do JOIN in SQL. A join is a fundamental operation in the SQL language. It's as important is understanding how to do a loop in other languages.

SELECT DISTINCT users.user_name
FROM users JOIN entries USING (user_id)
WHERE entries.header = 'foo';

Regarding your second question, no, you can't make table names or column names dynamic within a single statement.

However, you can write code in your application to build a SQL statement as a string, based on looking up column names and table names. Then execute the resulting string as a new SQL query.