CF Multiple Queries, Multiple Records

[OnDemand Junkie]

All,

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#

Thanks

D

[mxstu]

You can use the CF ValueList() function to create a comma delimited list of all
IDs returned by the previous query. Then use the SQL IN() function to return
all records where the column value equals any one of the IDs in the returned
list.

Do you need to keep the data in separate queries? You could probalby combine
your 3 query statements into 1.

<!--- needs error handling - ex. if "PRODCAT" query returns 0 records --->
SELECT Name
FROM Products
WHERE Product_ID IN (#ValueList(PRODCAT.Product_ID)#)

[stephend]

Hi

Assuming you don't need all three queries, you could do something like this

SELECT
Cat.Name, Cat.Category_ID ,
prodcat.ID, prodcat.Product_ID, prodcat.Category_ID,
prod.NAME
FROM
Categories cat,
Product_Category prodcat,
Products prod
Where
prodcat.Category_ID = cat.Category_ID and
prod.Product_ID = prodcat.Product_ID