title: mySQL view derived from multiple tables with GROUP_CONCAT
body: <p>How would I code the query to yield the mySQL VIEW 'inventory' below from the following 3 mySQL tables:</p>
  
  <p>Table: <strong>product</strong></p>
  
  <pre><code>product_id    category_id   brand
   1              1            Aylmer
   2              2            Ace
   3              2            Weston
   4              1            Campbell
   5              3            Vachon
   6              4            Hero
   7              5            Lactancia
   8              4            Welch
   9              4            Bonne Maman
  10              4            Smucker
  </code></pre>
  
  <p>Table: <strong>category</strong></p>
  
  <pre><code>category_id   name_en   name_fr
       1         soup     soupe
       2         bread    pain
       3         cake     gateau
       4         jam      confiture
       5         butter   beurre
  </code></pre>
  
  <p>Table: <strong>other_name</strong></p>
  
  <pre><code>other_name_id    category_id     alternate_category
        1               1                 broth
        2               1                 stock
        3               2                 roll
        4               2                 bun
        5               2                 loaf
        6               4                 sweet spread
        7               5                 salty spread
  </code></pre>
  
  <p>View: <strong>inventory</strong></p>
  
  <pre><code>product_id   name_en     name_fr      brand       alternate_categories      
         1       soup       soupe       Aylmer        broth; stock
         2       bread      pain        Ace           roll; bun; loaf
         3       bread      pain        Weston        roll; bun; loaf
         4       soup       soupe       Campbell      broth; stock
         5       cake       gateau      Vachon        
         6       jam        confiture   Hero          sweet spread
         7       butter     beurre      Lactancia     salty spread
         8       jam        confiture   Welsh         sweet spread
         9       jam        confiture   Bonne Maman   sweet spread
        10       jam        confiture   Smucker       sweet spread
  </code></pre>