5 Replies Latest reply on Aug 29, 2014 2:07 AM by BKBK

    Can you combine multiple queries into one?

    skinneyfat Level 1

      I have a database table of options for additions to homes. I have another table for categories for these options. Right now I'm making a lot of queries to get each individual category of options. It looks something like this:

       

      </cfquery>

      <cfquery datasource="applewood" username="username" password="password" name="getCategory2" >

           SELECT *

           FROM op_categories WHERE idOpCategories = 3

       

      </cfquery>

      <cfquery datasource="applewood" username="username" password="password" name="getCategory3" >

           SELECT *

           FROM op_categories WHERE idOpCategories = 4

       

      </cfquery>

      ...and so on. Is there a way to make 1 query that will find each WHERE clause? I want it to look like this on one page:

       

      Category 1          Category 2          Category 3

      ---------------         -----------------          ----------------

      Option 1             Option 1               Option 1

      Option 2             Option 2               Option 2

      Option 3             Option 3               Option 3

       

       

      Any help would be appreciated.

        • 1. Re: Can you combine multiple queries into one?
          gokul1242 Level 1

          What about this..

           

          <cfquery datasource="applewood" username="username" password="password" name="getAllCategory" >

               SELECT *

               FROM op_categories WHERE idOpCategories in (3,4)

          </cfquery>

          • 2. Re: Can you combine multiple queries into one?
            BKBK Adobe Community Professional & MVP

            We need more information. Do the categories have the same number of options? How are Option 1, Option 2, etc. related?

             

            If you require just a number of option values to build the display, why then do you use 'select *' instead of, say, 'select opID, idOpCategories'? It would help to know more about the columns in op_categories and how they are related.

            • 3. Re: Can you combine multiple queries into one?
              skinneyfat Level 1

              The options category has the actual options plus a FK of the categories. So the category would be "dishwashers" and the options would be "power scrub dishwasher", "Dishwasher". Each category can have multiple options in them. You can see  short example at the following address http://www.applewoodcommunity.com/textareaTest.cfm. This shows the format I want to accomplish. Right now each category/option section has it's own query statement.

              • 4. Re: Can you combine multiple queries into one?
                skinneyfat Level 1

                I spoke before I tried. thank you, it worked great.

                • 5. Re: Can you combine multiple queries into one?
                  BKBK Adobe Community Professional & MVP

                  skinneyfat wrote:

                   

                  The options category has the actual options plus a FK of the categories. So the category would be "dishwashers" and the options would be "power scrub dishwasher", "Dishwasher". Each category can have multiple options in them. You can see  short example at the following address http://www.applewoodcommunity.com/textareaTest.cfm. This shows the format I want to accomplish. Right now each category/option section has it's own query statement.

                  Why mention any categories at all? From the example you show, I would in fact go for the zero-brainer:

                   

                  <!--- Incidentally, the name of your query says it all! --->

                  <cfquery datasource="applewood" username="username" password="password" name="getAllCategory" >

                       SELECT *

                       FROM op_categories

                  </cfquery>

                   

                  Then use query-of-a-query to pick out the categories as, and when, you need them. This offers you flexibility and the economy of just one trip to the database.

                   

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

                  SELECT *

                  FROM getAllCategory

                  WHERE idOpCategories = 3

                  </cfquery>

                   

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

                  SELECT *

                  FROM getAllCategory

                  WHERE idOpCategories = 4

                  </cfquery>

                   

                  <cfoutput query="cat3">

                  <!--- Do yer category 3 stuff here --->

                  </cfoutput>

                   

                  <cfoutput query="cat4">

                  <!--- Do yer category 4 stuff here --->

                  </cfoutput>