title: Trouble with outer join coupled with search coupled with group by
body: <p>I am trying to do 2 things with a query. </p>
  
  <p>1) Return the ticket subject from one table and the matching messages from another table. This is a one to many relationship and I want the return to show the subject with all the messages for each subject concatenated.</p>
  
  <p>2) be able to search against the subject as well as all its notes for a search string.</p>
  
  <p>3) some subjects do not have messages so the join needs to be a left join, but if a search matches I ONLY want the records it matches returned not all the other records from the right table.</p>
  
  <p>This is my attempt which fails because it doesn't concatenate the messages and it returns extraneous records that don't match the search criteria:</p>
  
  <pre><code>select t.seq, t.`subject`, n.message from tickets t left join tickets_notes n
  on t.seq = n.ticket_seq and (t.`subject` like '%search string%' or n.message like '%search string%')
  and t.seq=98795 GROUP BY t.seq;
  </code></pre>
  
  <p>I appreciate any advice you can offer. (Please note: this query is run against millions and millions of records, so efficiency is a big issue.)</p>