title: Criteria,Independent Subqueries, Group By and Having more
body: <p>I have a product table with a relationship to color table</p>
  
  <p>a product can have many colors... exp: Product A: has red, green blue yellow.</p>
  
  <pre><code>Product
  -----------
  ProductID
  Name
  
  Color
  -----------
  ProjectID
  Color
  </code></pre>
  
  <p>I wish to find the product which contain at least RED and GREEN </p>
  
  <p>for dynamic size, I also wish to add name = "doggy"</p>
  
  <p>The SQL statement is </p>
  
  <pre><code>select * from product pd where pd.ProductID = (select cr.productID from color cr where cr.color="RED" or cr.color="GREEN" group by  cr.productID having rowcount=2)
  and name like '%doggy'
  </code></pre>
  
  <p>How do I create the Criteria, without dependent subqueries.</p>
  
  <p>Question linked to</p>
  
  <p>1) <a href="https://stackoverflow.com/questions/11426562/criteria-subqueries-group-by-and-having-more/11427513#comment15074949_11427513">Criteria, Subqueries, Group By and Having more</a></p>
  
  <p>2) <a href="https://stackoverflow.com/questions/11409302/one-to-many-search-using-and-condition/11411560#comment15076601_11411560">One to Many search using AND condition</a></p>
  
  <p>As you can see, there is no good solution for this issue.</p>