CODEWITHSUNDEEP
coldfusion
user3591637
user3591637

Reputation: 499

How to join two queries from different databases without qoq?

I have two different queries 

  <cfquery datasource="#one#" name="GetResults">
    SELECT ID, Account, Value
      FROM Logger_Events
      left join Event_Details
      on ID =  EventID
      where type = '123'

    </cfquery>

    <cfquery datasource="#two#" name="getSubtotal">
      select account from daily 
    </cfquery>

The first query gives me like 200 records , the second query gives me like half a million records.

Is there a way i can make the second query get the records from the first query, without making a query of queries? I would be using the account column to join.

The tables are in different servers.

Upvotes: 1

Views: 530

Answers (3)

Antonio
Antonio

Reputation: 931

I have done this function to Replace the foreign key of a table for a text field contained in the foreign table. This could instead append new columns to the original query with QueryAddColumn() and QuerySetCell().

The idea is to convert one of the queries into a struct. My function is pretty basic but at the moment looks something like this:

<cffunction name="joinNameFromIDStructs" returnType="query" hint="Returns the Fund Activity">
        <cfargument name="qryMain" type="query" required="Yes" hint='The query which is going to be translated' >
        <cfargument name="foreignTable" type="string" required="Yes" hint='table which contains the details of our foreign column' >
        <cfargument name="foreignPK" type="string" required="No" default="#arguments.foreignTable#ID" hint="Primary Key in the foreign table" >
        <cfargument name="foreignKey" type="string" required="Yes" hint="column in the main table which references another table" >
        <cfargument name="foreignText" type="string" required="Yes" hint="the column in the foreing table which hold the required details. It will override the foreign key column in the main table" >

        <cfset var idPK = "">
        <cfset var qryForeignTable = "">
        <cfset var stForeignTable = StructNew()>
        <cfset var listForeignPKs = evaluate("ValueList(arguments.qryMain.#arguments.foreignKey#)")/>
        <cfif ListLen(listForeignPKs)>
            <cfquery name="qryForeignTable" datasource='#application.siteDataSource#' >
                SELECT #arguments.foreignPK#, #arguments.foreignText# FROM #arguments.foreignTable#
                    WHERE #arguments.foreignPK# IN (<cfqueryparam cfsqltype="cf_sql_integer" value='#arrayToList(listToArray(listForeignPKs))#' list="true" />) <!--- CF8 does not support ListRemoveDuplicates , hence we will use the array conversion to remove empties --->
            </cfquery>      
            <cfloop query='qryForeignTable'>
                <cfset stForeignTable[qryForeignTable[arguments.foreignPK][qryForeignTable.currentrow]] = qryForeignTable[arguments.foreignText][qryForeignTable.currentrow]/>
            </cfloop>
            <cfloop query='arguments.qryMain'>
                <cfset idPK = arguments.qryMain[arguments.foreignKey]/>
                <cfif idPK neq "">
                    <cfset arguments.qryMain[arguments.foreignKey][arguments.qryMain.currentrow] = stForeignTable[idPK]/>
                </cfif>
            </cfloop>
        </cfif>
        <cfreturn arguments.qryMain/>
</cffunction>

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Since you are using sql server, I suggest linked servers. Pick one of your servers and set up a linked server to the other one. Then set up a stored procedure that does something like this;

declare #TempTable table (field1 datatype1, etc);

insert into #TempTable (field1, field2, etc)
select field1, field2, etc
from openquery
(linkedServerName, 
'select field1, field2 etc
 from blah blah blah
 ')

select yourfields
from #TempTable join aTableInThatDB on etc

The details are kind of painful at first, but it works very well. Also, there is lot's of information regarding linked servers and openquery on the internet.

Upvotes: 2

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

This is one of the reasons for a QoQ. You could run the 1st query, then convert the data to XML and pass it as a parameter to a stored proc on the 2nd DB server in order to filter the 2nd query directly on the DB. Just join to the XML or use the XML to populate a temp table.

Upvotes: 5

Related Questions