title: CFQuery 'WHERE' from results of another query?
body: <p>I'll explain the 'real life' application of this so it's easier to understand.  </p>
  
  <p>I'm working on an eCommerce app that has a category structure.  It starts at the top level and gradually moves down through subcategories.  For example Home > Electronics > TVs > Plasma</p>
  
  <p>I'm using a single page for this, showing the 'home' page if no category is defined, the subcategories if there are any, and finally the products if there are no subcategories.</p>
  
  <p>This all works fine, however when I get to the 2nd part - displaying subcategories, the page is a little empty.  Therefore, I'd like to display a selection of products that span all of the subcategories applicable.</p>
  
  <p>This is where I'm struggling - in most cases, there will be a few subcategories.  However, I'm not sure how to structure the 'where' query using the results of the previous query (code snippets below for reference).</p>
  
  <p>I don't believe QofQ would be worth exploring, and I've made a vain attempt at doing something with substrings, without success.</p>
  
  <p>Any pointers much appreciated!</p>
  
  <pre><code>&lt;cfquery name="getcategories"&gt;
  SELECT p.ID AS CategoryID, p.Cat_Name as CategoryName, p.Cat_Shortname, c.ID AS SubCategoryID, c.Cat_Name as SubCategoryName, c.Cat_Shortname AS SubCatShortname
  FROM    product_categories p LEFT JOIN product_categories c ON p.ID = c.SubcategoryOf
  WHERE  p.SubcategoryOf = 0
  &lt;/cfquery&gt;
  
  &lt;cfif IsDefined('url.cat')&gt;
  &lt;!--- Look for additional subcategories ---&gt;
  &lt;cfquery name="getsubcategories"&gt;
  SELECT *
  FROM product_categories
  WHERE Subcategoryof='#url.cat#'
  &lt;/cfquery&gt;
  &lt;cfquery name="getproducts"&gt;
  SELECT *
  FROM products
  WHERE categoryid='#url.cat#'
  ORDER BY RAND()
  &lt;/cfquery&gt;
  &lt;/cfif&gt;
  </code></pre>