title: SMSS- Creating new table by combining three tables and adding new column with conditions based on multiple columns
body: <p>I am using SQL Server Management Studio and I am trying to create new table by combining three tables and adding new column with based on another column. Please see example:</p>
  <p>Starting Tables
  Table 1:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Name</th>
  <th>Allergies</th>
  <th>Food Budget</th>
  <th>State</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Alice</td>
  <td>Chocolate</td>
  <td>$50</td>
  <td>NY</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Cindy</td>
  <td>Seafood</td>
  <td>$25</td>
  <td>CA</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Tim</td>
  <td>Apples</td>
  <td>$30</td>
  <td>HI</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Andrew</td>
  <td>Nuts</td>
  <td>$45</td>
  <td>NJ</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table 2:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Menu Choice</th>
  <th>Price</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Chocolate</td>
  <td>$5</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Sandwich</td>
  <td>$26</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Apples</td>
  <td>$8</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Fish</td>
  <td>$50</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table 3:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>State</th>
  <th>High Cost of Living?</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>NY</td>
  <td>Yes</td>
  </tr>
  <tr>
  <td>NJ</td>
  <td>No</td>
  </tr>
  <tr>
  <td>CA</td>
  <td>Yes</td>
  </tr>
  <tr>
  <td>AK</td>
  <td>No</td>
  </tr>
  <tr>
  <td>AZ</td>
  <td>No</td>
  </tr>
  <tr>
  <td>HI</td>
  <td>Yes</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I am looking for is to create a table combining all three table with a new column:
  Final table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Name</th>
  <th>Allergies</th>
  <th>Food Budget</th>
  <th>State</th>
  <th>Menu Choice</th>
  <th>Price</th>
  <th>High Cost of Living?</th>
  <th>Notes</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Alice</td>
  <td>Chocolate</td>
  <td>$50</td>
  <td>NY</td>
  <td>Chocolate</td>
  <td>$5</td>
  <td>Yes</td>
  <td>Allergic</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Cindy</td>
  <td>Seafood</td>
  <td>$25</td>
  <td>CA</td>
  <td>Sandwich</td>
  <td>$26</td>
  <td>Yes</td>
  <td>Good</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Tim</td>
  <td>Apples</td>
  <td>$30</td>
  <td>HI</td>
  <td>Apples</td>
  <td>$8</td>
  <td>Yes</td>
  <td>Allergic</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Andrew</td>
  <td>Nuts</td>
  <td>$45</td>
  <td>NJ</td>
  <td>Fish</td>
  <td>$50</td>
  <td>No</td>
  <td>Good</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Notes will be based on looking at Allergies. If Allergies= Menu choice, then put &quot;Allergic&quot; or else put &quot;Good&quot;.</p>
  <p>Thank you in advance for the help!</p>