I’m sure I’ve come across this issue before, where I need to reference a 2nd or [n] database for additional information (using a join) when I will be getting the [n] database name from the source query initially.
Cryptic? Here’s the SQL I’m using (that’s not working, of course). I think the problem I’m having is the ‘dbname.[tablename]‘ is not valid (the ‘dbname’ part).
-
SELECT
-
dl.insertdate,
-
dl.userID,
-
dl.imageId,
-
dl.dbID,
-
-
db.id,
-
db.dbname AS dbname,
-
-
sg.Record_ID,
-
sg.Name,
-
-
rid.Record_ID,
-
rid.Value,
-
-
it.Record_ID,
-
it.Filename
-
-
FROM
-
image_server.CE_download_log dl
-
INNER JOIN
-
image_server.CE_dblist db
-
ON
-
db.id = dl.dbID
-
-
INNER JOIN
-
dbname.record_ids_168 rid
-
ON
-
rid.Value = dl.imageID
-
INNER JOIN
-
dbname.Saved_Galleries sg
-
ON
-
sg.Record_ID = rid.Record_ID
-
INNER JOIN
-
dbname.Item_Table it
-
ON
-
it.Record_ID = rid.Value
-
WHERE
-
dl.userID = ‘cedge’
Also, here’s the dbmodel diagram that I’m using.
Update: I’ve decided to take the cheap way out and split this problem into two queries, using PHP to set the $database_name variable.
Sigh, I hate to do it, but that seems to be the fastest way to get this issue resolved so users can begin to run their reports. Speed to market sometimes takes precedence over quality.
Categories