title: Make a 1 to 1 multi-field SQL join where only some of the values match
body: <p>I am trying to build a table that will be used as a conversion chart. I aim to make a simple join with this conversion table on multiple fields (8 in my case), and get a result. I will try to simplify the examples as much as I can because the original chart is a 40x10 matrix.</p>
  <p>Let's say that I have these two (I know they don't make much sense and have bad design but they are just examples):</p>
  <pre><code>supply_conversion_chart
  ---
  supply (integer)
  customer_id (integer)
  product_id (integer)
  size (varchar)
  purchase_type (varchar)
  </code></pre>
  <pre><code>purchases
  ---
  customer_id (integer)
  product_id (integer)
  size (varchar)
  purchase_type (varchar)
  </code></pre>
  <p>and conversion chart would look something like this:</p>
  <pre><code>| supply | customer_id  | product_id | size     | purchase_type |
  |--------|--------------|------------|----------|---------------|
  | 100    | 1            | anything   | anything | online        |
  | 101    | 1            | anything   | anything | offline       |
  | 102    | other than 1 | anything   | anything | online        |
  | 103    | 1            | 5          | XXL      | online        |
  </code></pre>
  <p>The main goal was to get an exact <code>supply</code> value by simply doing a join by doing something like:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT supply
  FROM purchases p
           JOIN supply_conversion_chart scc ON
          p.customer_id = scc.customer_id AND
          p.product_id = scc.product_id AND
          p.size = scc.size AND
          p.purchase_type = scc.purchase_type;
  </code></pre>
  <p>Let's say that these are the records on <code>purchases</code> table:</p>
  <pre><code>| customer_id | product_id | size | purchase_type |
  |-------------|------------|------|---------------|
  | 1           | 3          | M    | online        |
  | 1           | 5          | S    | offline       |
  | 12345       | 4          | XL   | online        |
  | 1           | 5          | XXL  | online        |
  | 4353        | null       | M    | online        |
  </code></pre>
  <p>I would expect first record's supply value to be <code>101</code>, second record's to be <code>102</code>, third <code>102</code>, fourth <code>103</code>, and fifth to be <code>102</code>. However, as far as I know, SQL won't be able to do a proper join on all of these records except the fourth one, which is fully matching with supply <code>103</code> on <code>supply_conversion_chart</code> table. I don't know if it is possible in the first place to do a join using multiple fields when some of those fields are not fully matching.</p>
  <p>My approach is probably faulty and there are better ways to get the results I am trying to achieve but I don't even know where to start. What should I do?
  The original chart is much bigger that the provided example, and that I will be doing a join on 8 different fields.</p>