Can you combine multiple queries into one?

[skinneyfat]

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.

[gokul1242 · Accepted solution]

What about this..

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

     SELECT *

     FROM op_categories WHERE idOpCategories in (3,4)

</cfquery>

[skinneyfat]

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

[BKBK]

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.

[skinneyfat]

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]

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>