MYSQL SELECT Statement Newbie

Question

Is there a way I can:

SELECT (SELECT * from table2) FROM table1

In table2, I have a list of columns I would like to select from table1, just like the ones below:

Week starting 24/01/2015,Week starting 31/01/2015,Week starting 07/02/2015,Week starting 14/02/2015,Week starting 21/02/2015,Week starting 28/02/2015,Week starting 07/03/2015,Week starting 14/03/2015,Week starting 21/03/2015,Week starting 28/03/2015,Week starting 04/04/2015,Week starting 11/04/2015

you can start with this search: https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=sql%20select%20column%20names%20from%20table — Seth McClaine, Apr 23 '15 at 14:53
Are you saying that the columns in table1 are actually called `Week starting 24/01/2015` etc or do you want to see the contents where a column contains 'Week starting 24/01/2015' etc? — Giles, Apr 23 '15 at 14:54
not possible. you cannot use the results of a subquery as the field list of another query. you can write a stored procedure to build an sql string, but that's probably way beyond you right now. And the names of your fields suggest you need to learn how to set up a proper database. splitting a table into multiple smaller ones isn't always a bad thing, but doing it just for "organizational" purposes is almost always a bad idea. — Marc B, Apr 23 '15 at 14:54
Isn't it better to retrieve the column names from table2 and then creating another request to table 1? — Ronnie, Apr 23 '15 at 14:54
So: in table 1 I have a list of weekly statuses (column names change every week, based on approvals) weekA weekB weekC weekD a a a a Now, I have some code that give me a list of columns that i'm interested in (starting with `week`) in table 2: `weekA`,`weekB`,`weekC`,`weekD` My question was how can I select in table 1 only the column names I've identified in table 2. Thanks ! — cnstlungu, Apr 23 '15 at 16:30

score 1 · Accepted Answer · edited May 23 '17 at 10:24

I think this answer has the information you are looking for:

How to select column names dynamically in mySQL:

Try this SQLFiddle:

CREATE TABLE atable (
  prefix1 VARCHAR(10)
  ,prefix2 VARCHAR(10)
  ,notprefix3 INT
  ,notprefix4 INT
);

INSERT INTO atable VALUES ('qwer qwer', 'qwerqwer', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'asdfaasd', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'qrt vbb', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'sdfg sdg', 1, 1);

SELECT CONCAT('SELECT ', GROUP_CONCAT(c.COLUMN_NAME), ' FROM atable;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'atable'
  AND c.COLUMN_NAME LIKE 'prefix%'
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;

EXECUTE stmt;

Some issues:

You will likely want some kind of ORDER BY on your result set.

There's a limit to what you can do in terms of joins and things.

You move validation to runtime where it's more likely to be missed by testing.

You are hoping to be able to handle schema changes easily. This technique will only handle schema changes of a certain type you can foresee, and others you will probably have to change this code anyway.

MYSQL SELECT Statement Newbie

1 Answers1