title: SQLiteStudio: Obtaining the most recent price from a price catalog
body: <p>I'm fairly new to SQL and am trying to get the price for a product transaction on a particular date my looking up the most recent price of that product prior to the transaction within a price catalog. </p>
  
  <p>Specifically, I have the two following tables:</p>
  
  <pre><code>Transactions                                Catalog 
  ----------------------------------------------------------------------------
  ProductID | Design     | Transaction_DT     ProductID | Price | Effective_DT
  
  1         | Plaid      | 5/14/2016          1         | 20    | 4/22/2016
  2         | Solid      | 3/26/2016          1         | 10    | 5/2/2016
  3         | PolkaDot   | 4/12/2016          1         | 5     | 5/15/2016
  4         | Solid      | 4/24/2016          2         | 50    | 3/22/2016
  5         | PolkaDot   | 2/24/2016          2         | 25    | 4/1/2016
  6         | PinStripe  | 3/29/2016          2         | 10    | 4/2/2016
                                              3         | 30    | 4/5/2016
                                              3         | 25    | 4/9/2016
                                              3         | 22    | 4/12/2016
                                              4         | 12    | 3/15/2016
                                              4         | 8     | 3/27/2016
                                              4         | 6     | 4/25/2016
                                              5         | 15    | 2/23/2016
                                              5         | 11    | 2/25/2016
                                              5         | 6     | 2/28/2016
                                              6         | 26    | 2/2/2016
                                              6         | 17    | 3/19/2016
                                              6         | 13    | 5/16/2016
  </code></pre>
  
  <p>I have entered the following code:</p>
  
  <pre><code>SELECT Transactions.ProductID,
         Catalog.Price,
         Transactions.Transaction_DT,
         Transactions.Design
    FROM Transactions
         LEFT JOIN
         Catalog ON Transactions.ProductID = Catalog.ProductID AND
         Catalog.Effective_DT = (
                                    SELECT MAX(Effective_DT)
                                      FROM Catalog
                                     WHERE Effective_DT &lt;= Transactions.Transactions DT
                                )
  </code></pre>
  
  <p>And obtained the following output:</p>
  
  <pre><code>ProductID | Price | Transaction_DT | Design
  
  1         | Null  | 5/14/2016      | Plaid
  2         | 50    | 3/26/2016      | Solid
  3         | 22    | 4/12/2016      | PolkaDot
  4         | Null  | 4/24/2016      | Solid
  5         | 15    | 2/24/2016      | PolkaDot
  6         | Null  | 3/29/2016      | PinStripe
  </code></pre>
  
  <p>I would like to return the Price for products 1, 4, and 6 to be 10, 8, and 17 respectively (in addition to the correct prices which were properly output) instead of the Null values I'm getting. Any ideas on how I can obtain the proper results?</p>