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).

  1. SELECT
  2. dl.insertdate,
  3. dl.userID,
  4. dl.imageId,
  5. dl.dbID,
  6.  
  7. db.id,
  8. db.dbname AS dbname,
  9.  
  10. sg.Record_ID,
  11. sg.Name,
  12.  
  13. rid.Record_ID,
  14. rid.Value,
  15.  
  16. it.Record_ID,
  17. it.Filename
  18.  
  19. FROM
  20. image_server.CE_download_log dl
  21. INNER JOIN
  22. image_server.CE_dblist db
  23. ON
  24. db.id = dl.dbID
  25.  
  26. INNER JOIN
  27. dbname.record_ids_168 rid
  28. ON
  29. rid.Value = dl.imageID
  30. INNER JOIN
  31. dbname.Saved_Galleries sg
  32. ON
  33. sg.Record_ID = rid.Record_ID
  34. INNER JOIN
  35. dbname.Item_Table it
  36. ON
  37. it.Record_ID = rid.Value
  38. WHERE
  39. dl.userID = ‘cedge’

Also, here’s the dbmodel diagram that I’m using.sql dbmodel

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.

Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • StumbleUpon
  • Technorati
  • email
  • NewsVine