How do I join two tables that belong to two different databases?

[Guest]

I can build an SQL statement in SQL Server Management Studio that does a join of a table in one database with a table in another database.  I have a common "read" username/password for both databases.

How do I do the join in ColdFusion?  The <cfquery> tag requires a single datasource.  I could do it as two queries and cross comparing, or even possibly some usage of dbtype=query, but these would be horrendously less efficient than a direct join.

I could place the SQL statement in a stored procedure, but that seems like overkill-- especially since the number of fields needed in the "WHERE" clause will change based on logic in the ColdFusion program.

Does anyone know of a way of doing this with <cfquery>?

[ilssac · Accepted solution]

How do you do it in SQL Server Management Studio?  You must be doing something I am not aware of, if what you do there does not work in a <cfquery...> block.

The way I have done this in the past is just to fully qualify the table names.  I.E. server.databaseOwner.database.table if I recall correctly.

This does require that the the database connected to by the data source name is configured to be allowed to connect to the other database.

[Adam Cameron.]

Provided the user the datasource uses has permissions to both DBs, you just do it exactly the same way you would on any other DB client.  CF doesn't do anything with the SQL other than pass it to the DB...

--

Adam

[Dan_Bracuk]

If the database permissions do not allow you to do it the way Adam and Ian described, then you run these three queries.

query1 dsn1

select etc

query2 dsn2

select etc

where somefield in (valuelist(query1.something)

query3

query of queries joining query1 and query 2

[JR__Bob__Dobbs-qSBHQ2]

If you are using Microsoft SQL Server you would qualify the table names with the database and schema.  So if you have Table1 in DB1 and Table2 in DB2 and both belong to the default dbo schema you query would look like:

SELECT T1.Field1, T2.Field2

FROM DB1.dbo.Table1 AS T1 INNER JOIN DB2.dbo.Table2 AS T2

     ON ( T1.A = T2.A )

WHERE T1.B = 'X'

Be aware that the SQL login associated with your ColdFusion datasource will need permissions to access both databases and the objects needed for your queries.

Perhaps you can post the query as it appears in SQL Management Studio.

Message was edited by: JR "Bob" Dobbs Corrected query syntax