title: SQL Recursive CTE using two tables
body: <p>I have two tables, one is a Product Customer mapping and another is a Product sharing table. Using SQL recursive CTE, given a product, I am trying to find all the products that are linked as a chain, mapped across the customers. In addition, if a Product is shared with another Product, I need to include it in the chain too. Hopefully, my example make more sense than the description</p>
  
  <pre><code>Product Customer Table
  Product    Customer
    Milk     Illinois
    Milk     Michigan
    Butter   Michigan
    Cream    Wisconsin
    Honey    Wisconsin
    Cheese   Minnesota
  
  Product Sharing Table
  Product    SharedProduct
   Butter     Cream
   Cream      Cheese
  </code></pre>
  
  <p>For the above data, let's say my input Product is Milk, then the result set should include all the products - Milk, Butter, Cream, Honey &amp; Cheese. Here Butter-Cream &amp; Cream-Cheese are linked through the Product Sharing table.</p>
  
  <p>My current SQL looks like this but doesn't really work more than one level deep.</p>
  
  <pre><code>WITH Product_CTE AS
  (
    SELECT DISTINCT [Product] FROM ProductCustomer
    WHERE [Product] IN (SELECT DISTINCT p2.[Product]
    FROM ProductCustomer p1 INNER JOIN ProductCustomer p2
    ON p1.[Customer] = p2.[Customer] WHERE p1.[Product] = 'Milk')
    UNION ALL
    SELECT [SharedProduct] FROM ProductSharing b
    INNER JOIN Product_CTE p ON p.[Product] = b.[Product]
  )
  Select [Product] from Product_CTE
  </code></pre>