Left outer join from two datasources

[RobCatman]

This is an age old question, and we're currently using an age old version (CF9), upgrading soon.  I'm a developer of 25 years but have only been using CF for about 8 mos.

I have to get data from two different datasources and join them together.  Outer join, of course.  I've read many topics on this.  I had originally done this by looping at a query of the main table and selecting from a query from second table.  My supervisor & former CF programmer doesn't want us to use a loop/select. They first suggested a UNION of the two tables but that's not giving me the results I need.

I thought of selecting from one table then inserting them into a temp table on the second datasource but when I try to do an insert into the temp table then selecting from the query of the first table, CF tells me I can't do that either with error "Executing the SQL statement is not allowed."

Any suggestions on correct ways to outer join tables from two datasources without using a loop?

Thanks from "The new guy."

-=Rob=-

[WolfShade]

Hello, Rob,

First of all, I'd like to mention that selecting from two different datasources, as you have put in your question, isn't possible, AFAIK.  If you mean from two different TABLES in the same datasource, then yes, that can be done very easily.  And I totally agree with your supervisor.  Iterating through one query to get information from another query is high overhead.  It's more CPU intensive.

How you join, though, depends upon what you want.  If you want to get information from both tables regardless of whether or not related records are in both, you do a LEFT OUTER JOIN.  If you don't want records from the left table that don't have a corresponding record(s) in the right table, then a standard LEFT JOIN will skip records in the left table that do not have a corresponding record(s) in the right table.

HTH,

^ _ ^

[RobCatman]

Thanks WolfShade. I've been using SQL based languages for 20 years so I understand how to use joins between two tables. Thank you for your answer, though.

I have to join them from two different datasources.  I can do a QoQ and do a regular join, but it's that darn outer join that's killing me.  Like I said, I've seen this posted many times but I haven't found a actual solution other than using loops.

I was hoping the thouoght of inserting data from a query into a temp table on the second datasource would work, but it's telling me I can't execute that.

[WolfShade]

Yeah, between two actual datasources, you can't do a JOIN or a UNION, AFAIK. Sadly, in this situation, I believe the loop is the only option. It's my understanding that even with full permissions granted, two different datasources cannot interact with each other.. not even to put data into a temp table.

[RobCatman]

I can do some of that in a QoQ. It's that stinkin outer join that's killin' me. Thanks for the input!

[John123]

You can query each table from separate datasources then use the two queries in a Query of Queries. Unfortunately you cannot directly do a left outer join in Q of Q but there is an article on how to get the same result at the following https://coldfusionblog.wordpress.com/2012/01/11/how-to-do-an-outer-join-in-query-of-queries/

[haxtbh]

Are the two datasources on the same SQL instance?

If so its possible just doing a normal query.

SELECT table.field1, table.field2, table.field3, databasename.dbo.tablename.field1 AS otherField1
FROM table
LEFT OUTER JOIN databasename.dbo.tablename ON databasename.dbo.tablename.field1 = table.field
WHERE table.field1 = 1

[RobCatman]

Sadly, no. 😞

[EddieLotter]

In SQL Server I create "linked servers" so that I can join tables from multiple servers. Perhaps your database engine has a similar feature?