Write query without join and using subquery
<p>I want to rewrite the join query with sub-query like <code>IN()</code>, <code>ANY()</code> operator.</p>

<p>Data set:</p>

<pre><code>*categories*
categoryID name 
         5 ROD    
         7 CEMENT 

*products*
productID categoryID name      
        7          5 BSRM 10mm 
        9          5 KSRM 5mm  
       10          5 Julius    
       11          7           
       12          5 BSRM 25mm 

*sale_products*   
saleID productID 
   118         9 
   119         9 
   120         9 
   121         9 
   122        12 
   123        12 
   124        12 
</code></pre>

<p>This is my query to read saleID, product name and category name for which already have sold.
<a href="https://i.stack.imgur.com/oHICV.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oHICV.jpg" alt="enter image description here"></a></p>

<p>My query is:</p>

<pre><code>SELECT sale_products.saleID, products.name, categories.name
FROM categories
  INNER JOIN (products, sale_products)
    ON  categories.categoryID = products.categoryID
    AND products.productID = sale_products.productID 
</code></pre>

<p>Now I want to the result set without join and with the sub-query methodology.</p>

<p>I try in this way:</p>

<pre><code>SELECT categories.name
FROM categories
WHERE categories.categoryID IN
          (SELECT products.categoryID
           FROM products
           WHERE products.productID in
               (SELECT sale_products.productID FROM sale_products))
</code></pre>

<p>this query only give me the category name but I need also saleID, product name.</p>