MySQL: SELECT . LEFT JOIN CONCAT(.

Trying to retrieve a table name within a JOIN

[Marino]

Hello,

I have two tables.

"sites"
id
dn
charset
info
lang
table_name
last_visit

and some other tables, following the 'table_name' field in the 'sites' table
id
dn
charset
info
https
lang
title
metas
content
last_visit

I'm trying to left join two tables. The problem is that I need to have the 'table_name' field to be evaluated dynamically.
I thought it could be achieved through a CONCAT(), but no way:

SELECT
`sites`.`id` AS siteId,
`sites`.`dn`AS siteDN,
`sites`.`charset`AS siteCharset,
`sites`.`info`AS siteInfo,
`sites`.`table_name`AS siteTableName,

CONCAT('`',siteTableName,'`').`lang`AS siteLang,
CONCAT('`',siteTableName,'`').`title`AS siteTitle,
CONCAT('`',siteTableName,'`').`metas`AS siteMetas,
CONCAT('`',siteTableName,'`').`content`AS siteContent

FROM
`sites`

LEFT JOIN
CONCAT('`',siteTableName,'`') ON CONCAT('`',siteTableName,'`').`id`=siteId

WHERE
`sites`.`dn` LIKE '%.edu';

Is there a way to get this working?

Regards,

Marino

[ZydoSEO]

I'm not sure if this will help. But it looks like you can use PREPARE and EXECUTE to build dynamic SQL with dynamic table names. Below is an example I found of a stored procedure that takes a table name for a parameter and then dynamically queries the table:

DELIMITER ¦
create procedure test (IN Rule INT, IN RuleTable varchar(25), IN min INT)
BEGIN
DECLARE num_v INT;
SET @num_v=-1;
SET @dyn_sql=CONCAT('Select count(*) into @num_v from ',RuleTable,' where TimeStamp > (now() - INTERVAL ? MINUTE)');
PREPARE s1 from @dyn_sql;
SET @min_val=min;
EXECUTE s1 USING @min_val;
DEALLOCATE PREPARE s1;
IF( @num_v < 1) THEN
SET @dyn_ins="insert into EventEngineAlerts values ('',?,now(),0)";
PREPARE s2 from @dyn_ins;
SET @rule_val=Rule;
EXECUTE s2 USING @rule_val;
DEALLOCATE PREPARE s2;
END IF;
END;

[Marino]

Thanks, I'm not found of stored procedure, for I never got one run fast, but I'll give it a try.

Thanks again,

Marino