Left outer join from two datasources

Explorer ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques, Database access

Views

1.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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.

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 16, 2019 Sep 16, 2019

Copy link to clipboard

Copied

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/

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 19, 2019 Sep 19, 2019

Copy link to clipboard

Copied

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

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 19, 2019 Sep 19, 2019

Copy link to clipboard

Copied

Sadly, no. 😞

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 24, 2019 Sep 24, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines