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?
Oh, didn't know that. How about nesting QofQ's? Join Query1 and Query2 into a new query, then join that to Query 3?
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.
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,
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?
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
Copy link to clipboard
Copied
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!