How to choose a dynamic table name

Question

I have tables like

changes201101 changes201102 changes201103 ... changes201201 

And a table whichchanges that contain the rows Year and MONTH

How can I select * from changes from whichchanges ?

I am typing this query

 SET @b := SELECT CONCAT('changes',year,month) FROM whichchanges; ((((@b should contain now multiple rows of changesYearMonth))))) SET @x := SELECT * FROM @b; Prepare stmt FROM @b; Prepare stmt FROM @x; Execute stmt; 

# 1064 - You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax used next to “FAVORITES CHANGES” (“changes”, “year”, “month”) on line 1

Answer 1

You open 1 ( and close 2 ) . Delete last:

 SELECT CONCAT('changes',year,month) FROM changes 

Edit

the second statement should be

 SET @x := SELECT * FROM (@b) as b; 

This works, but not sure if this is what you want:

 SET @b := 'SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges'; SET @x := 'SELECT * FROM (SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges) as b'; Prepare stmt FROM @b; Prepare stmt FROM @x; Execute stmt; 

Edit2

If I understood correctly, you are looking for this single request:

 select * from changes where change_column in (select distinct concat(`year`, `month`) from whichchanges) 

Edit3

 select @b := group_concat(concat(' select * from changes', `year`, `month`, ' union ') separator ' ') as w from whichchanges; set @b := left(@b, length(@b) - 6); Prepare stmt FROM @b; Execute stmt; 

SQLFiddle example