title: How to select from joined MySQL tables?
body: <p>I basically have two tables:</p>
  
  <pre><code>A(id int, name varchar(10), info varchar(10))
  B(id int, item varchar(10))
  
  A
  1 A Hello
  2 B World
  3 C Foo
  
  B
  1 apple
  1 orange
  1 hammer
  2 glass
  2 cup
  </code></pre>
  
  <p>Using the following query...</p>
  
  <pre><code>mysql&gt; SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
         FROM a
         LEFT OUTER JOIN b ON (a.id=b.id)
         GROUP BY 1,2,3;
  </code></pre>
  
  <p>I get the desired output:                                              </p>
  
  <pre><code>+------+------+-------+------------------------------------+
  | id   | name | info  | group_concat(b.item separator ' ') |
  +------+------+-------+------------------------------------+
  |    1 | A    | Hello | apple orange hammer                | 
  |    2 | B    | World | glass cup                          | 
  |    3 | C    | Foo   | NULL                               | 
  +------+------+-------+------------------------------------+
  </code></pre>
  
  <p>I would now like to modify the query to get a result table containing the same information but only those who have items starting with the letter o, <code>(like 'o%')</code> How do I get the following output?</p>
  
  <pre><code>+------+------+-------+------------------------------------+
  | id   | name | info  | group_concat(b.item separator ' ') |
  +------+------+-------+------------------------------------+
  |    1 | A    | Hello | apple orange hammer                |                        
  +------+------+-------+------------------------------------+
  </code></pre>
  
  <p>Note that I still want to present the whole string <code>'apple orange hammer'</code>, not only <code>'orange'</code></p>