0

I am trying to use the results of a query in the SELECT clause.

I query the information_schema to get the column names to select. I then use CONCAT and GROUP_CONCAT to format the results into the following form:

table1.column1,table1.column2,table1.column3,table1.column4

Here is the statement:

SELECT Group_concat(DISTINCT Concat("table1.", column_name)) 
FROM   information_schema.columns 
WHERE  table_name = "table1";

Now, I would like to use these results in a query like this:

SELECT (SELECT Group_concat(DISTINCT Concat("table1.", column_name)) 
        FROM   information_schema.columns 
        WHERE  table_name = "table1") 
FROM   table1;

This incorrectly returns the following results:

+-----------------------------------------------------------------------------------------------------------------------------+
| (SELECT Group_concat(DISTINCT Concat("table1.", column_name)) FROM information_schema.columns WHERE  table_name = "table1") |
+-----------------------------------------------------------------------------------------------------------------------------+
| table1.column1,table1.column2,table1.column3,table1.column4                                                                 |
| table1.column1,table1.column2,table1.column3,table1.column4                                                                 |
| table1.column1,table1.column2,table1.column3,table1.column4                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------+

Where it should return results similar to the results of: 

SELECT * FROM table1;

It seems like this person managed to get it working, but I was unable to find my error from that question.

Note: I want to do this to eventually have a script that can dynamically JOIN tables where the table names are only known at runtime, in order to report all data that is affected by a single change.

Community
  • 1
  • 1
Ian2thedv
  • 2,691
  • 2
  • 26
  • 47
  • You can't compute the column names to select like that. `GROUP_CONCAT` just returns a string, this is not parsed as part of the query. – Barmar Apr 14 '15 at 13:17
  • You need to write dynamic SQL using `PREPARE`. – Barmar Apr 14 '15 at 13:17
  • I don't see anything in that other question like what you've written. – Barmar Apr 14 '15 at 13:18
  • @Barmar the person managed to have a subquery in the select clause, I did not know that my issue was with `GROUP_CONCAT`. Is there a way I can change the results of the nested query to be parsed as part of the outer query? – Ian2thedv Apr 14 '15 at 13:23
  • 1
    You can use nested queries, but you can't compute the column names to select. You have to use `PREPARE` to parse a query string. – Barmar Apr 14 '15 at 13:27
  • 1
    I think the goal you try to achieve is out of regular mysql. check here: http://stackoverflow.com/questions/4428761/mysql-field-name-from-variable – Alex Apr 14 '15 at 13:31
  • @Barmar I have managed to get the correct results using prepared statements. I used the question that Alex posted. Thank you for the help. I will accept and your answer if you chose to provide an answer with example. But seeing that it is a possible duplicate... its up to you – Ian2thedv Apr 14 '15 at 13:44
  • I'll reopen the question and then @Alex can mark it as a duplicate of the other question. – Barmar Apr 14 '15 at 13:45

0 Answers0