I discovered this a bit by accident. I was wondering if it was possible to query 2 MYSQL databases in the one CFQUERY as I had 2 tables that I wanted to join in 2 separate databases and instead of moving the tables in question into the same database I thought I’d have a play… here are the results:

Lets make things nice and simple and call the databases database1 and database2… it’s easier. I should also mention that the 2 databases are located on the same MYSQL server and are accessed with the same user account. I have a Coldfusion datasource connected to database1 called database1connector. Here is the query and the syntax, it’s all about aliases people!

<cfquery name="q" datasource="database1connector">
	SELECT tb1.name,tb2.address
	FROM table1 tb1
	INNER JOIN database2.table2 tb2
		ON tb2.id  = tb1.id
</cfquery>

Note that I include the second database by name in the join clause, simply referring to it by ‘databasename.’ then the table name. Note that I don’t use the ‘databasename.’ notation for the database being accessed via the CF datasource (the first database, ie database1 in the above example) as it will error.

I haven’t tried it but I am guessing that it would be possible to essentially join multiple databases using this same setup, give it a go!