Skip to main content
S
skinneyfat
Inspiring
August 26, 2014
Answered

Can you combine multiple queries into one?

  • August 26, 2014
  • 2 replies
  • 776 views

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.

    This topic has been closed for replies.
    Correct answer gokul1242

    What about this..

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

         SELECT *

         FROM op_categories WHERE idOpCategories in (3,4)

    </cfquery>

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 28, 2014

    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.

    S
    skinneyfatAuthor
    Inspiring
    August 28, 2014

    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.

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 29, 2014

    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>

      gokul1242
      gokul1242Correct answer
      Inspiring
      August 28, 2014

      What about this..

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

           SELECT *

           FROM op_categories WHERE idOpCategories in (3,4)

      </cfquery>

      S
      skinneyfatAuthor
      Inspiring
      August 28, 2014

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

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices