title: How to populate datatable with all possible instances of certain data?
body: <p>I am having some trouble writing a SQL Server stored procedure. I have three tables, <code>Products</code>,<code>CustomerInfo</code> and <code>CustomerOrders</code>. </p>
  
  <ul>
  <li><code>Products</code> has columns <code>ProductID,Product, Price, Description</code></li>
  <li><code>CustomerInfo</code> has columns <code>CostumerID, Name, Address, Zipcode</code> </li>
  <li><code>CustomerOrders</code> has columns like <code>CustomerID or TransactionID, ProductID, Quantity</code>.</li>
  </ul>
  
  <p>Now I am trying to write a stored procedure which will import into a datatable all the products the customer has ever bought. I have the customers name that I can use as an parameter. </p>
  
  <p>Couple of things to note: every time a customer purchases something, a new <code>CustomerID</code> and <code>TransactionID</code> are generated and they are both the same. The <code>CustomerName</code> is the only constant across multiple orders.</p>
  
  <pre><code>DECLARE @TransactionID int;
  
  SET @TransactionID = @Id;
  
  SELECT 
      P.Product, P.Price, 
      CP.TotalProducts as ProductQuantity 
  FROM 
      Products P
  INNER JOIN 
      CustomerProducts CP ON CP.ProductID = P.ProductID
  WHERE
      CP.CustomerID = @TransactionID
  </code></pre>
  
  <p>At the moment, I get the products that the customer bought the last time he shopped. However, I want to get all products he has ever bought into one table. If anyone can help me out, I would really appreciate it!</p>