Highlighted

Can you combine multiple queries into one?

Contributor ,
Aug 26, 2014

Copy link to clipboard

Copied

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.

What about this..

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

     SELECT *

     FROM op_categories WHERE idOpCategories in (3,4)

</cfquery>

Views

363

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Can you combine multiple queries into one?

Contributor ,
Aug 26, 2014

Copy link to clipboard

Copied

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.

What about this..

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

     SELECT *

     FROM op_categories WHERE idOpCategories in (3,4)

</cfquery>

Views

364

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 26, 2014 0
Explorer ,
Aug 28, 2014

Copy link to clipboard

Copied

What about this..

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

     SELECT *

     FROM op_categories WHERE idOpCategories in (3,4)

</cfquery>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 28, 2014 0
Contributor ,
Aug 28, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 28, 2014 0
Adobe Community Professional ,
Aug 28, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 28, 2014 0
Contributor ,
Aug 28, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 28, 2014 0
BKBK LATEST
Adobe Community Professional ,
Aug 29, 2014

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 29, 2014 1