How to join tables from different datasources

[Lotruth]

I need to query for information from two tables in two different datasources. I can I go about doing this? I am using Access 2000.

 

[ChiefJoseph]

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.

 

[khazmir]

It sounds like you're asking how you will query the table that is not part of the datasource you specify in your cfquery, ex:

<cfquery name=&quot;my_query&quot; datasource=&quot;#information#&quot;>

If you call the table from a different datasource by it's complete name, database.dbo.table, you should be able to access it:

select *
from table1, differentdatabase.dbo.table2

 

[CFtaxtron]

I believe Lotruth has two two different databases accessed through two different datasources so he would like to do something like

<cfquery name=&quot;my_query&quot; datasource=&quot;datasource1, datasource2&quot;>

but of course that code won't work. :-(

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.

Tim

 

[khazmir]

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.

Later!

 

[GunJack]

Hey Lotruth,

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 &quot;query of queries&quot; 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 &quot;queryNew()&quot;. The problem with this approach is that you can't sort easily and joins are trickier.

Good luck,
GJ

 

[khazmir]

Specify servername.database.dbo.table for the server that is not specified as your datasource.

Example:
<cfquery name=&quot;my_query&quot; datasource=&quot;#information#&quot;>
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.

 

[TechDude]

Khazmir,
I don't think you can do that from access
-chris
Chris Sorel
chris@exnihilo.com

http://www.exnihilo.com

Remember, If you continue to do what you have always done,

you will continue to get what you have always gotten.

 

[Guest_imported]

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..

Hope this helps in some way

 

[strantheman]

Query of queries baby!

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.

This is just an example.

<!--- access --->
<CFQUERY name=&quot;getApples&quot; datasource=&quot;ds1&quot;>
select color,weight,origin from table_apples
</CFQUERY>

<!--- oracle --->
<CFQUERY name=&quot;getOranges&quot; datasource=&quot;ds2&quot;>
select circumference,weight,origin from table_oranges
</CFQUERY>

<!--- get a combined record set filtered from the two previous record sets --->
<CFQUERY name=&quot;combined&quot; datasource=&quot;query&quot;>
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>

 

[gunawandjon]

In Access:
Create a link table that link to other database (using odbc).

The new table then can be queried as it is in the first database.