XML to query with inner join

Question

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?

Carl Von Stetten · Accepted Answer

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

Carl Von Stetten · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.