Nested MySQL FROM query with two DBs I need to display user-specific tables for users logged into a CMS. These tables are stored in another database with tables for the different user groups. For example User 1 to 4 should get data from Table1, User 5-12 from Table2 etc. Unfortunately I can only run one query because of the CMS plugin creating charts only supports one. I can pass the UserID into that query though.

Tables:


UserTable matches User IDs to Group IDs (User1 = Group99).
GroupTable matches Group IDs to table names (Group 99 = Table3).
These User specific tables (Table3) contain Datetimes and Values.


This query returns the values I need from Table3 for example:

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM *DB.TableX*
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)


DB.TableX should now be replaced with the result from the following query, which basically returns "Table3":

SELECT grouptable.tablename FROM usertable
LEFT JOIN grouptable ON grouptable.ID = usertable.groupID
WHERE User = %USERID%


When I try to nest the second query into the FROM statement of the first, I get an error "Unknown column timestamp in field list":

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM
  (SELECT grouptable.tablename FROM cmsdb.usertable
  LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID
  WHERE User = 2) AS SUBQUERY
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)


I thought that was possible but I can't figure out what's wrong. I don't want to build a seperate page for each user :-/
as described it will not work, but You can use prepared statements for this type of operations:

SET @tb_name = (SELECT grouptable.tablename FROM cmsdb.usertable LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID WHERE User = 2);


just keep in mind subquery above, must return single value, if not sure - add LIMIT 1

set @query = CONCAT('SELECT DATE(timestamp) As Date, AVG(Value) AS Value FROM ',  @tb_name , ' WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE() GROUP BY DATE(timestamp)');

prepare stmt1 from @query;
execute stmt1;

deallocate stmt1;