title: How to do join on multiple criteria, returning all combinations of both criteria
body: <p>I am willing to bet that this is a really simple answer as I am a noob to SQL.</p>
  
  <p>table 1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)</p>
  
  <p>table 2 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 2 specific to table2.criteria2)</p>
  
  <p>There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table.</p>
  
  <p>when I use the join statement here (assuming I identify table 1 as One prior to this):</p>
  
  <pre><code>Select WeddingTable, TableSeat, TableSeatID, Name, Two.Meal
  FROM table1 as One
  inner join table2 as Two
  on One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat
  </code></pre>
  
  <p>I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4.  How do I get all combinations?</p>
  
  <p>Take the situation where there is a wedding where table 1 is basically a seating chart, and table 2 is the meal option that each table/seat has chosen. Table 1 has the convenient TableSeatID, but Table 2 does not have a comparable ID.</p>
  
  <p><strong>Sample Data:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/I5IRy.jpg" alt="enter image description here"></p>
  
  <p>The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.</p>
  
  <p><strong>Desired Results:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/c1dr2.png" alt="enter image description here"></p>