skyrun
Active Member
environment:
note how the tablename on the join doesn't have a the db name of the join specified on it. also note the error message incorrectly puts the 'site database' name (vs the db name of the joined db from the connection), so that's causing an error since that table isn't on the 'main' database, but instead on the other db i'm trying to join to.
UPDATE: tested and if i expressly specify the name of the db on the join as in ...LEFT JOIN dbfromconnection.'2ndtablename'... etc... on the SQL generated vs. not specifying which means it defaults to the site db, then the SQL works. so i think you're perhaps not specifying the db name on joins that are created by adding a databasejoin element that refers to a different database (which i agree is an uncommon use case to test).
- j!3.1.1 f3.1
- have 3 connections setup to 3 different databases (all on same db server).
- added a databasejoin element (location element on campaigns form in app if you want to take a look) which joins to a table using a different connection. then when i display the table i get this error:
Code:
1146 Table '[B]sitebname[/B].2ndtablename' doesn't exist SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `1sttablename`.`id` AS `1sttablename___id`,
...more columns...
` LEFT JOIN `2ndtablename` AS `2ndtablename` ON `2ndtablename`.`id` = `1sttablename`.`location` LIMIT 0, 10
note how the tablename on the join doesn't have a the db name of the join specified on it. also note the error message incorrectly puts the 'site database' name (vs the db name of the joined db from the connection), so that's causing an error since that table isn't on the 'main' database, but instead on the other db i'm trying to join to.
UPDATE: tested and if i expressly specify the name of the db on the join as in ...LEFT JOIN dbfromconnection.'2ndtablename'... etc... on the SQL generated vs. not specifying which means it defaults to the site db, then the SQL works. so i think you're perhaps not specifying the db name on joins that are created by adding a databasejoin element that refers to a different database (which i agree is an uncommon use case to test).