title: Searching for parent records whose children meet predicate
body: <p>Let's say I have a parent and child database, and the child keeps a sort of running transcript of things that happen to the parent:</p>
  
  <pre><code>create table patient (
     fullname text not null,
     admission_number integer primary key
  );
  
  create table history (
     note text not null,
     doctor text not null,
     admission_number integer references patient (admission_number)
  );
  </code></pre>
  
  <p>(Just an example, I'm not doing a medical application).</p>
  
  <p><code>history</code> is going to have many records for the same <code>admission_number</code>:</p>
  
  <pre><code>admission_number      doctor    note
  ------------------------------------
  3456                  Johnson   Took blood pressure
  7828                  Johnson   EKG 120, temp 99.2
  3456                  Nichols   Drew blood
  9001                  Damien    Discharged patient
  7828                  Damien    Discharged patient with Rx
  </code></pre>
  
  <p>So, my question is, how would I build a query that let me do and/or/not searches of the note field for <code>patient</code> records, like, for example, if I wanted to find every patient whose history contained "blood pressure" and "discharged".</p>
  
  <p>Right now I'm been doing a <code>select</code> on <code>history</code> that groups by <code>admission_number</code>, combining all the notes with a <code>group_concat(note)</code> and doing my search in the <code>having</code>, thus:</p>
  
  <pre><code>select * from history 
    group by admission_number
    having group_concat(note) like '%blood pressure%' 
       and group_concat(note) like '%discharged';
  </code></pre>
  
  <p>This works, but it makes certain elaborations very complicated -- for example, I'd like to be able to ask things like "every patient whose history contains "blood pressure" and whose history with Dr. Damien says "discharged," and building qualifications like this on top of my basic query is very messy.</p>
  
  <p>Is there any better way of phrasing my basic query?</p>