Retriving matching list of data from two database

[thousandfive]

Hi
Team,

I have problem in retriveing matches data from two data base.I m using coldfusion MX .Databases are access and sql.

This is my example coding

<cfquery name="test" datasource="db1">
Select call_key,description from db1
</cfquery>

<cfquery name="test1" datasource="db2">
Select cso,model from db2
</cfquery>

So far it is working.

Problem started here.

I want to retrive those records from two database where call_key and cso are matching.

<cfquery dbtype="query name="result">
Select test.*,test1.* from test,test1 inner join test
where test.call_key=test.cso
</cfquery>


Above coding is not working..
Error .

Query Of Queries syntax error.
Encountered "inner


Do you have any idea of this?Any alternative ways?

thank you

five

[bMind]

I dont work with access, but in mySQL, it would look like this...

Select t.call_key,t1.cso FROM test t LEFT JOIN test1 t1 ON t.call_key=t1.cso

Try this..

Select test.*,test1.* from test inner join test1
where test.call_key=test1.cso

They have the same concept, you just have to play around a lil.

Hope that helps