MYSQL SELECT Newbie Announcement

Question

Is there any way:

SELECT (SELECT * from table2) FROM table1

In table2I have a list of columns that I would like to select from table1, as shown 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

Answer 1

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

How to dynamically select column names in mySQL :

Try 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 problems:

You will probably need some sort of ORDER BY in your result set.

There is a limit to what you can do in terms of associations and things.

, .

. , , , , , .