How can I use two datasources in a CFQUERY?

Question

I am using ColdFusion 9.1.

I need to use two different datasources in some of my queries. I know it can be done because I see other code that uses two different datasources and it works fine.

I've tried lots of combinations but can't get anything to work, but I know that both of my datasources are working properly.

I have a default database set up in the THIS scope. The default is "DatasourceOne".

<cfquery>
SELECT UserID
FROM   DatasourceOne.TableOne IN (SELECT Userid FROM DatasourceTwo.TableTwo )
</cfquery

What are the rules or guidelines about using multiple datasources?

CLARIFICATION

I should have originally asked how I could use two database (not datasources) in a single query. I am sure your answers would have been different. We do have both databases set up as datasources though and I was a little confused myself.

Answer 1

Depending on your database, if the second database is on the same server (or is defined as a linked server) and the user in the datasource has permission, you can usually reference the other database.

SELECT * FROM myTable 
WHERE myField IN 
(SELECT otherField FROM otherDatabase.dbo.tableName)

Answer 2

You can't talk to two CF (JDBC) datasources in a single CFQUERY. What you can do:

• Use two databases on the same datasource. For example, if you have a SQL Server instance with two database you can run a query through the JDBC connection that talks to both databases. This looks like what you're describing in your question. Here's a more thorough explanation.
• Use Queries of Queries. Pull your data from the two database individually and join the results using a QoQ in your CFC or page.

Answer 3

ColdFusion can only talk to one data*source* at a time in a given query. However, if you need to talk to more than one data*base* on the same server, you can do that by explicitly giving the full paths to the databases, tables, and columns you need to access or join together. Also note that the user that the data*source* in ColdFusion is configured to use must have access to both databases in order for this to work.