HostMySite.Com is sponsoring this tutorial, please visit their site today!

Combining two queries into one.. by: Pablo Varando

[Download in PDF Format] [Download in FlashPaper Format]

A question I get often is if I have two databases on two separate "datasources" that have similar data, how can I create a single query from it so I can display my records easier?

Well, the easiest thing that comes to mind is the following tutorial. The first thing you will need to do is create your two queries for the two databases.

<cfquery name="query1" datasource="FirstDSN">
    SELECT         FirstName,
                       LastName,
                       Email
    FROM            Members
    ORDER BY      FirstName
</cfquery>

<cfquery name="query2" datasource="SecondDSN">
    SELECT         FirstName,
                       LastName,
                       Email
    FROM            Members
    ORDER BY      FirstName
</cfquery>

Now that you have your TWO queries, the next step is to create a query from scratch and input these values, that way you can output them and work with your data as if it was a single query you performed.

The first thing you must do is to create the query, you achieve this as follows:

<!--- create the new query from scratch --->
<cfset myNewQuery = QueryNew("FirstName, LastName, Email")>

Next we will create the rows for the query.

<!--- make some rows in the query --->
<cfset newRow = QueryAddRow(MyNewQuery, #query1.RecordCount# + #query2.recordCount#)>

Notice the right variables "#query1.RecordCount# + #query2.recordCount#"  this is specifying that this new query we are creatin will have a total number of rows that both of the prior queries return. This will allow this to know the new recordcount.

The next step is to actually create a counter, I will show you why this is needed in a bit, for now lets define it to have a default value of "0".

<cfset counter = 0>

The next step is to go through the first TWO queries and populate the new query with data.

<!--- set the cells in the query --->
<cfoutput query="query1">
     <cfset counter = counter + 1>
     <cfset temp = QuerySetCell(myQuery, "FirstName", query1.FirstName, counter)>
     <cfset temp = QuerySetCell(myQuery, "LastName", query1.LastName, counter)>
     <cfset temp = QuerySetCell(myQuery, "Email", query1.Email, counter)>
</cfoutput>
<!--- set the cells in the query --->
<cfoutput query="query2">
     <cfset counter = counter + 1>
     <cfset temp = QuerySetCell(myQuery, "FirstName", query2.FIrstName, counter)>
     <cfset temp = QuerySetCell(myQuery, "LastName", query2.LastName, counter)>
     <cfset temp = QuerySetCell(myQuery, "Email", query2.email, counter)>
</cfoutput>

<!--- Then you can use this as a regular query --->
<cfoutput query="MyNewQuery">
      #currentrow#) #name# - #ddress# - #phone#<BR>
</cfoutput>

That's pretty much it, you now have a new query called "MyNewQuery" that you can work like any other query you've ever worked with...

Questions? Comments? Email me...

HostMySite.Com is sponsoring this tutorial, please visit their site today!

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: