5 Replies Latest reply on Mar 21, 2016 5:17 AM by BreakawayPaul

    XML to query with inner join

    BreakawayPaul Level 2

      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?

        • 1. Re: XML to query with inner join
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: XML to query with inner join
            BreakawayPaul Level 2

            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,

            • 3. Re: XML to query with inner join
              Carl Von Stetten Adobe Community Professional & MVP

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

              • 4. Re: XML to query with inner join
                BreakawayPaul Level 2

                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

                • 5. Re: XML to query with inner join
                  BreakawayPaul Level 2

                  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!