4 Replies Latest reply on Nov 14, 2006 11:10 PM by kruse

    Merge two query results

    kruse
      Is it possible to merge 2 query results?

      I have a function that returns a querey result.

      < CFSET temp = query1>
      <CFSET FNCTransfers = temp>

      Now I want to change the query to return a merged query result

      < CFSET temp = query1>
      <CFSET temp2 = query2>

      Is it possible to merge the two results?

      Some thing like this (I know that it cannot be done like this)
      <CFSET FNCTransfers = temp1 & temp2>

      Maby it should be a union and query of query?


        • 1. Re: Merge two query results
          monkey_woo_too Level 1
          You will most likely find trying to use Query of Query very problematic. I reccommend that you actually write a SQL statement to do the union at the DB end.

          I know that it seems a bit crappy, but i think it will help you maintain your sanity.
          • 2. Re: Merge two query results
            Dan Bracuk Level 5
            The best way to achieve your goal depends on what it is. Your question is very general.
            • 3. Re: Merge two query results
              Level 7
              UNION could be the way to go. the query results will have to match up
              between the 2 queries (field data types need to match for both queries)

              <cfquery name="qryMergedQueries" dbtype="query">
              SELECT * FROM query1
              UNION
              SELECT * FROM query2
              </cfquery>


              kruse wrote:
              > Is it possible to merge 2 query results?
              >
              > I have a function that returns a querey result.
              >
              > < CFSET temp = query1>
              > <CFSET FNCTransfers = temp>
              >
              > Now I want to change the query to return a merged query result
              >
              > < CFSET temp = query1>
              > <CFSET temp2 = query2>
              >
              > Is it possible to merge the two results?
              >
              > Some thing like this (I know that it cannot be done like this)
              > <CFSET FNCTransfers = temp1 & temp2>
              >
              > Maby it should be a union and query of query?
              >
              • 4. Re: Merge two query results
                kruse Level 1
                I had two different queryes getting internal transgers in a company and external transfers in/out of a company and then I would show a merged list of this two results.
                The two queryes vere very complex and therfore I would not merge the two queryes into one query.

                The query of query aproach worked very well.