title: SQL count occurrences of values grouped by external tables references
body: <p>What is the best approach in terms of performance and maintainability to count the number of occurrences of the same value in a table, grouping the results with the same reference that groups the entries of the table?</p>
  
  <p>Let's say I have three tables (concepts have been shrinked in order to represent a scenario that is similar to the one I'm working on):</p>
  
  <pre><code>|----------|   |----------------|   |-----------------------------------|
  |   MEAL   |   |      RECIPE    |   |          INGREDIENT_ENTRY         |
  |----------|   |----------------|   |-----------------------------------|
  | ID | ... |   | ID | ID_m | ...|   | ID | ID_r | amount and description|
  |----------|   |----------------|   |-----------------------------------|
  |  1 | ... |   |  1 |    1 | ...|   |  1 |    1 |       '15gr of yeast' |
  |  2 | ... |   |  2 |    2 | ...|   |  2 |    4 |              '2 eggs' |
  |  3 | ... |   |  3 |    3 | ...|   |  3 |    1 |      '300cl of water' |
  |  4 | ... |   |  4 |    4 | ...|   |  4 |    2 |       '300cl of beer' |
  |----------|   |  5 |    1 | ...|   |  5 |    3 |       '250cl of milk' |
                 |  6 |    4 | ...|   |  6 |    5 |   '100gr of biscuits' |
                 |  7 |    5 | ...|   |  7 |    2 |       '15gr of yeast' |
                 |  8 |    6 | ...|   |  8 |    1 |      '500gr of flour' |
                 |----------------|   |  9 |    2 |      '500gr of flour' |
                                      | 10 |    2 |        '10gr of salt' |
                                      | 11 |    4 |       '15gr of yeast' |
                                      |-----------------------------------|
  </code></pre>
  
  <p>The same MEAL can be cooked with a different RECIPE, and each RECIPE is made of different INGREDIENT_ENTRYs, organized in the same RECIPE by sharing the same ID_r value. </p>
  
  <p>INGREDIENT_ENTRY.[amount and description] is a column of type VARCHAR(MAX), this is the value that must be compared.</p>
  
  <p>In the example, making the query with (MEAL 1,RECIPE 1):</p>
  
  <p>It has 3 ingredients (1,3,8), and shares:</p>
  
  <ul>
  <li>Two ingredients with RECIPE 2 (7,9) -> and so can be found in MEAL 2</li>
  <li>One ingredient with RECIPE 4 (11) -> and so can be found in MEAL 3</li>
  </ul>
  
  <p>Result should look something like:</p>
  
  <pre><code>|------|   |--------|   |-------|
  | MEAL |   | RECIPE |   | COUNT |
  |------|   |--------|   |-------|
  |    2 |   |      2 |   |     2 |
  |    4 |   |      4 |   |     1 |
  |------|   |--------|   |-------| 
  </code></pre>
  
  <p>I'm experimenting with views to reduce SQL complexity, but I cannot make it with a single SQL statement and I would like to avoid going back and forth to code (C#) and perform multiple queries (for example query for every ingredient, and reconcile results with HashMaps or similar).</p>
  
  <p>Please, note that I cannot modify the DB structure.</p>