title: sql query tree like structure
body: <p>I have a tree like structure of Categories, the leaf Nodes in the tree have Products and the products have Cods
  I need to select all the top level Categories (parent=null) that have leafs (Cods) that match some critaria...</p>
  
  <pre><code>SELECT  
      Category.Id AS Id0_, 
      Category.Name AS Name0_, 
      Category.COrder AS COrder0_, 
      Category.Description AS Descript4_0_, 
      Category.ParentId AS ParentId0_, 
      Category.Description_En AS Descript6_0_, 
      Category.Name_En AS Name_En0_, 
      Category.ImagePath AS ImagePath0_ 
  FROM
      Category 
      LEFT JOIN Category AS c1 ON Category.Id=c1.ParentId
      LEFT JOIN Category AS c2 ON c1.Id=c2.ParentId
      LEFT JOIN Category AS c3 ON c2.Id=c3.ParentId
      LEFT JOIN Category AS c4 ON c3.Id=c4.ParentId
      LEFT JOIN Product ON 
          c4.Id=Product.Category 
          OR c3.Id=Product.Category 
          OR c2.Id=Product.Category 
          OR c1.Id=Product.Category 
          OR Category.Id=Product.Category
      INNER JOIN Cod ON Cod.Product=Product.Id   
  WHERE
      Category.ParentId is null 
      AND Cod.Hidden!='1' 
      AND 
      (
          cod.Stock&gt;0 
          OR (cod.CodBare='0' AND Product.ProdType=8)) 
          AND Cod.Price&gt;0
      )
  ORDER BY Category.COrder
  </code></pre>
  
  <p>my query looks like this, but it is not a solution because it is very very slow... Can someone give me a suggestion on how to do this?</p>