MySQL create a dynamic table name in a Join statement

Question

long time user, first time poster.

I have 2 tables;

a1_watchlists {id(PK),name,date}

a1_watchlist {id(PK),watchlists_id(FK(a1_watchlists.id)),company_name,asx_code,date}

I also have 2000 other tables that have been created with the name 'asx_'+[asx_code] (where asx_code is pulled from another table)

this table looks like; asx_[asx_code] {date(PK),open,high,low,close,volume}

I want to select all from a1_watchlists and a1_watchlist and then select the latest date from the asx_[asx_code] table using the value from a1_watchlist.asx_code to generate the [asx_code] part of the table name.

The problem I have is that I want to use the value from a1_watchlist.asx_code as the table name prepending the string 'asx_' to this first.

Closest I have been able to get is;

 DECLARE @TableName VARCHAR(100)
SELECT *
FROM a1_watchlist AS wl
JOIN a1_watchlists AS wls
  ON wls.id = wl.watchlists_id
  SET @TableName = 'asx_' + wl.asx_code
INNER JOIN (SELECT MAX(date),open,high,low,close,volume,amount_change,percent_change FROM @TableName)

This currently give the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @TableName VARCHAR(100)

SELECT *
FROM a1_watchlist AS wl
' at line 1

The expected colums I need in the final result would be:

wl.id,wl.watchlists_id,wl.company_name,wl.asx_code,asx_[asx_code].date,asx_[asx_code].open,asx_[asx_code].high,asx_[asx_code].low,asx_[asx_code].close,asx_[asx_code].volume

Let me know if you require more information.

score 1 · Answer 1 · edited May 23 '17 at 11:51

I'm not going to speak to what to do in the case where you have 2000+ tables that start with asx+ some code... (i live in a town with multiple bridges) or even whether what you're doing is the best way to get where you want to go. BUT, it does look like you're attempting to concatenate things together and create a dynamic statement. If that sounds right, then I'd recommend you look into prepared statements. Like the following. Hope this helps.

DELIMITER $$
DROP PROCEDURE IF EXISTS prRetrieveAllFromTable$$
CREATE PROCEDURE prRetrieveAllFromTable(tableName VARCHAR(64))
BEGIN

SET @s = CONCAT('SELECT * FROM ',tableName );
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;
CALL prRetrieveAllFromTable('calendar');

http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html

How To have Dynamic SQL in MySQL Stored Procedure

Cheers for the response Voidmain, Looks like you understand exactly what I am trying to achieve with the concat of a table name where part of the name comes from a field of an existing table. I am not exactly sure if what I am doing is the correct way either very new to MySQL. If instead of having the 2000 tables and I put them into 1 table I would have roughly 7 millions rows in 1 table which would grow by 2000 rows a day instead of 3500 in a single table like I have now which grows by 1 row a day. I will attempt what you have provided tonight — CassOwary, Feb 18 '15 at 04:12

MySQL create a dynamic table name in a Join statement

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @TableName VARCHAR(100)

1 Answers1