Chief here,
First your asking a VERY broad question, try to narrow it down next time.
Now are your tables related? if they arent then you will need two <CFQUERY> statements.
If they are related, you can refer to them through a join. Now this can be an inner join,outer join, or another that i cant remember.
A good way to learn how this is done is through the access query builder.
I'm curious to know how to do this as well, as I have two different sql servers one is the production server and the other one is a backup server. Once every four hours I copy the contents of one table on the production server onto the backup server and do a left join to figure out what accounts have been modified and then store the results for later use.
Obviously it would be nice if I could do the join without having to copy over all 20000 records onto the backup server first.
Hmm, you're right CFEmbanet. I was only talking about two different databases not datasources like I believed myself to be saying earlier.
I had this join problem before too but had totally forgotten that I actually had to copy a portion of the database from other datasource to the datasource I specified in my cfquery.
It seems that our DBA offered another solution that involved a stored procedure. I'll ask him for the specifics tomorrow at work and let you all know what he says.
Unfortunately, you can't easily execute a single query across multiple datasources in CF at the moment. CF 5.0 will allow you to do this with the "query of queries" functionality which is a great enhancement but is only available in the beta release right now.
You can try creating temp tables as CFHub suggests or you can try creating a custom query with the queryNew(), queryAddRow(), and querySetCell() functions. You could do two separate queries against each datasource and then loop through them and add a row at a time to the query created with "queryNew()". The problem with this approach is that you can't sort easily and joins are trickier.
Specify servername.database.dbo.table for the server that is not specified as your datasource.
Example:
<cfquery name="my_query" datasource="#information#">
select *
from table1 AS a INNER JOIN otherserver.differentdatabase.dbo.table2 AS b
ON a.city = b.city
</cfquery>
VERY IMPORTANT - The servers must be set up to allow remote access.
You can do this as a distributed query within SQL (presume using 7.0 / 2000) In the enterprise manager , under security add a linked server. You can setup an account to impersonate connection to your remote server . This server now exists in part in your overall schema . It can therefore be referenced as such hence khazmirs:
select *
from table1 AS a INNER JOIN otherserver.differentdatabase.dbo.table2 AS b
ON a.city = b.city
if you have a few linked servers , you can query anything all from one database!!!!
What is a good idea , is in the linked server properties tick the collation combatible option if your linking two sql servers , it will make the queries much faster..
This works only in CF 5.0 but it works! The first two queries are from two different databases. The third queries the results of the two and joins on the origin.
Query of queries type functionality can be accomplished using SQL server or other DBMS, but CF will let you combine ANY type of query including LDAP queries (CFLDAP), email queries (CFPOP), or even Verity collections using CFSEARCH.
<!--- get a combined record set filtered from the two previous record sets --->
<CFQUERY name="combined" datasource="query">
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.