design for style
MySQL query multiple databases
I thought it couldn't be done. I'd searched for it across the web. Terms such as mysql query multiple databases turned up nuthin' - that's right, not just nothing, but _flat-out_nuthin'_.
Today I was speaking with Matt Westgate of Lullabot about the challenges of managing 60+ Drupal web sites on a single codebase with each site having its own database, and Matt (who has a Drupal book coming out) mentioned liking MySQL for the fact that you could query multiple databases with a single query.
"Wha-? Really?" I said. But then it was time to go to my seat as the workshop put on by Matt's company was about to start and I didn't want to miss any of that gold.
But as the presenters ramped up, I began to play on my laptop with what I knew of the syntax for preventing ambiguities in MySQL, trying to guess at how a multi-database query would work, and lo and behold, I put something together that worked:
SELECT arbogast.node.nid, mcguffin.node.nid FROM arbogast.node, mcguffin.node WHERE arbogast.node.nid = 1 AND mcguffin.node.nid = arbogast.node.nid;
Note: arbogast and mcguffin are databases, node is a table common to each database, and nid is a field in each of those tables.
This is great news for me.
If someone could point me to some documentation on this subject, I'd appreciate it...
Perhaps a more fleshed out version of the query would be more meaningful:
SELECT arbogast.node.nid as anid, mcguffin.node.nid as mnid, arbogast.node.title as atitle, mcguffin.node.title as mtitle FROM arbogast.node, mcguffin.node WHERE arbogast.node.nid = 1 AND mcguffin.node.nid = arbogast.node.nid;