Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


XML to query with inner join

Contributor ,
Mar 18, 2016 Mar 18, 2016

Copy link to clipboard

Copied

I have a CF page that reads an XML file into a query using <cffile>, XMLParse, and QueryAddrow.

I then do a QoQ on the result, and voila! I have my page.  I cache the query for 8 hours and the XML file is only re-read when that cache expires.  It works well.

My problem now is that the file contains data from three database tables instead of one, so I need to do an inner join on the tables.  But I can't do a QoQ with an inner join.

Does anyone know a way for me to do this?

Views

655

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

correct answers 1 Correct answer

Guide , Mar 18, 2016 Mar 18, 2016
Oh, didn't know that.  How about nesting QofQ's?  Join Query1 and Query2 into a new query, then join that to Query 3?

Likes

Translate

Translate
Guide ,
Mar 18, 2016 Mar 18, 2016

Copy link to clipboard

Copied

You can effect an inner join using ANSI SQL syntax:

Assuming you have two queries to join, Query1 and Query2, and both tables have a common column "JoinCol" (it's not actually necessary for the column to be named the same in both tables as long as the data relates), you can do:

<cfquery name="JoinQuery" dbtype="query">

     SELECT Query1.Col1, Query1.Col2, Query2.Col1, Query2.Col2

     FROM Query1, Query2

     WHERE Query1.JoinCol = Query2.JoinCol

       AND {some other optional criteria here}

</cfquery>

If you have three queries to join, and the join between Query1 and Query2 is different from the join between Query2 and Query3, then something like this:

<cfquery name="JoinQuery" dbtype="query">

     SELECT Query1.Col1, Query1.Col2, Query2.Col1, Query2.Col2, Query3.Col1, Query3.Col2

     FROM Query1, Query2, Query3

     WHERE Query1.JoinColA = Query2.JoinColA

       AND Query2.JoinColB = Query3.JoinColB

       AND {some other optional criteria here}

</cfquery>

HTH,

-Carl V.

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
Contributor ,
Mar 18, 2016 Mar 18, 2016

Copy link to clipboard

Copied

Curiously, I tried that before.  This is the error I get:

Query Of Queries syntax error.
  Encountered ",. Detected more than two tables in the 'from' list 'cercat', Currently only 2 tables are supported,

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
Guide ,
Mar 18, 2016 Mar 18, 2016

Copy link to clipboard

Copied

Oh, didn't know that.  How about nesting QofQ's?  Join Query1 and Query2 into a new query, then join that to Query 3?

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
Contributor ,
Mar 18, 2016 Mar 18, 2016

Copy link to clipboard

Copied

I'll try that as soon as the CF server is back up. You can tell it's COB Friday when you get a 503 on the test server

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
Contributor ,
Mar 21, 2016 Mar 21, 2016

Copy link to clipboard

Copied

LATEST

Ok, so this seems to work.  I've tried it with three tables and it gives me exactly what I need.  I don't see why it wouldn't work with additional tables.

Thanks!

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