CF查询两个不同的数据库来匹配ID和Name

Question

我在不同的服务器上有两个数据库和两个表，我需要将它们组合起来以显示ID的名称。如何同时查询两个不同的数据库?

datasource="DB1, DB2" ?

所以这里我有一个查询，我如何结合数据库和表，以匹配我的op_id与assoc_name ?

    <cfquery name="processTable" datasource="DB1, DB2">
        SELECT *
        FROM [DB1].dbo.Dealer_Track_Work, [DB1].dbo.Dealer_Track_Dealers, [DB2].dbo.tco_associates
        WHERE [DB1]dbo.Dealer_Track_Work.dealerID = [DB1].dbo.Dealer_Track_Dealers.id,
              [DB1].dbo.Dealer_Track_Work.op_id = [DB2].dbo.Dealer_Track_Dealers.assoc_name,
              [DB1].dbo.Dealer_Track_Work.Closed_by = [DB2]dbo.Dealer_Track_Dealers.assoc_name      
    </cfquery>

Answer 1

您可以尝试这样做:

<CFQUERY NAME="getDetailsQuery1" DATASOURCE="DB1"> 
 SELECT * FROM getDetailsA 
</CFQUERY>

<CFQUERY NAME="getDetailsQuery2" DATASOURCE="DB2"> 
 SELECT * FROM getDetailsB 
</CFQUERY> 

<CFQUERY NAME="getDetails" DBTYPE="query"> 
 SELECT * FROM getDetailsQuery1 
 UNION 
 SELECT * FROM getDetailsQuery2 
 ORDER BY Name ASC 
</CFQUERY> 

给你所有的东西…

<CFQUERY NAME="getDetailsWhere" DBTYPE="query"> 
 SELECT * FROM getDetails where id = '123'
</CFQUERY>

功能:

<cffunction name="qDisplay" output="yes" access="public" returntype="query">
<cfargument name="id"   type="string"   required="yes"  />
<CFQUERY NAME="getDetailsQuery1" DATASOURCE="DB1"> 
     SELECT * FROM getDetailsA 
    </CFQUERY>

<CFQUERY NAME="getDetailsQuery2" DATASOURCE="DB2"> 
 SELECT * FROM getDetailsB 
</CFQUERY> 

<CFQUERY NAME="getDetails" DBTYPE="query"> 
 SELECT * FROM getDetailsQuery1 
 UNION 
 SELECT * FROM getDetailsQuery2 
 ORDER BY Name ASC 
</CFQUERY> 

<CFQUERY NAME="getDetailsWhere" DBTYPE="query"> 
 SELECT * FROM getDetails where id = #arguments.id#
</CFQUERY>
<cfreturn getDetailsWhere>
</cffunction>

希望这对你有帮助!