Inner Join on tables from different datasources (ms-sql + cfml)

[TechBoyJK]

Hi Guys,

I need to do an inner join, or achieve the same results, between two tables, each from a different datasource.


ex

tbl_1

id
member_id
info_a

tbl_2

member_id
info_b

Typically, I would do it like this

<CFQUERY NAME="myquery" DATASOURCE="data1" MAXROWS=1 >
SELECT tbl_1.id as id, tbl_1.member_id as member_id, tbl_1.info_a as info_a,
tbl_2.info_b as info_b
FROM tbl_dns
INNER JOIN tbl_2 on tbl_1.member_id = tbl_2.member_id
WHERE (member_id = <CFQUERYparam value="#member_id#">)
</CFQUERY>

But I can't because there are two different data sources in play.

What's the best way to go about this?

 

[Crusty]

Nested loops.

 

[drebo]

You can either do nested loops, or you can query the queries.

http://www.builderau.com.au/archite...in-ColdFusion-MX/0,339024547,320275211,00.htm

Which you do will depend on exactly what information you need and how you plan on querying it...which depends quite a bit on your data model, which you haven't really shared with us.