On 7/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I've got three queries all passing variables from previous queries. The
> issues?
> The second query returns 4 records. I want to use IDs for these records in
> query #3.
> However what I have only returns only 1 record. I want all 4 ID values in
> query #3
>
> <!-- GET EVERYTHING FROM CATEGORIES -->
> <!-- QUERY ONE -->
> <cfquery name="cats" datasource="cfweb" maxrows="5">
> SELECT Categories.Name, Categories.Category_ID
> FROM Categories
> </cfquery>
> <!-- GET INFO FROM PRODCAT WHERE CATID = CATS.CATID THIS SHOULD RETURN A LIST
> OF PROD IDS -->
> <!-- QUERY TWO -->
> <cfquery name="PRODCAT" datasource="cfweb">
> SELECT ID, Product_ID, Category_ID
> FROM Product_Category
> WHERE Category_ID = #CATS.Category_ID#
> </cfquery>
> <cfquery name="PROD" datasource="cfweb">
> SELECT NAME
> FROM Products
> WHERE Product_ID = #PRODCAT.Product_ID#
As the other CF-Talkers pointed out, you will have to use "valuelist".
But, I was wondering why you had three queries. The following query
(Oracle DB style) will list all products and their categories:
select
prodcat.id,
cat.name as category_name,
prodcat.category_ID,
prod.name as product_name,
prodcat.product_ID
from
categories cat,
products prod,
product_category prodcat
where
cat.category_id = prodcat.category_id and
prod.product_id = prodcat.product_id
So, instead of three queries, you can use just one, thereby minimizing
network round trips (assuming your web server and DB server are on
separate machines) and maximizing performance. You can even cache this
one query and use query-of-query whenever you want to select
information about one specific product ID.
Of course, I have no idea about the context of your application or the
usage of your queries within it. I was just giving you some ideas that
may or may not suit your needs.
--
Eddie.
http://awads.net/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211106
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
