CODEWITHSUNDEEP
sqlsqlite
iluvcapra
iluvcapra

Reputation: 9464

Searching for parent records whose children meet predicate

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:

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)
);

(Just an example, I'm not doing a medical application).

history is going to have many records for the same admission_number:

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

So, my question is, how would I build a query that let me do and/or/not searches of the note field for patient records, like, for example, if I wanted to find every patient whose history contained "blood pressure" and "discharged".

Right now I'm been doing a select on history that groups by admission_number, combining all the notes with a group_concat(note) and doing my search in the having, thus:

select * from history 
  group by admission_number
  having group_concat(note) like '%blood pressure%' 
     and group_concat(note) like '%discharged';

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.

Is there any better way of phrasing my basic query?

Upvotes: 2

Views: 385

Answers (3)

CL.
CL.

Reputation: 180210

This is similar to your EXISTS method, but computes the subqueries differently. This might or might not be faster, depending on how your tables and indexes are organized, and on the queries' selectivity.

SELECT *
FROM patient
WHERE admission_number IN (SELECT admission_number
                           FROM history
                           WHERE note LIKE '%blood pressure%')
  AND admission_number IN (SELECT admission_number
                           FROM history
                           WHERE note LIKE '%discharged%'
                             AND doctor = 'Damien')

Alternatively, you could use a compound subquery (computing the intersection once is likely to be faster than executing IN twice for every record):

SELECT *
FROM patient
WHERE admission_number IN (SELECT admission_number
                           FROM history
                           WHERE note LIKE '%blood pressure%'
                           INTERSECT
                           SELECT admission_number
                           FROM history
                           WHERE note LIKE '%discharged%'
                             AND doctor = 'Damien')

Upvotes: 1

iluvcapra
iluvcapra

Reputation: 9464

I have something -- using EXISTS to construct these is a bit cleaner:

select * from patients where 
    exists ( 
      select 1 from history where
        history.admission_number == patients.admission_number
        AND
        history.note LIKE '%blood pressure%'
    )
    AND
    exists (
        select 1 from history where
        history.admission_number == patients.admission_number
        AND 
        history.note LIKE '%discharged%'
        AND
        history.doctor == 'Damien'
    );

That's much better, now I can construct really fine-grained predicates.

Upvotes: 1

Grigorii Chudnov
Grigorii Chudnov

Reputation: 3112

Why don't you use a JOIN operation?

e.g. considering, the patient table contains the following data:

INSERT INTO patient VALUES('Bob', 3456);
INSERT INTO patient VALUES('Mary', 7828);
INSERT INTO patient VALUES('Lucy', 9001);

Running the query:

SELECT DISTINCT p.fullname, p.admission_number FROM patient p
INNER JOIN history h ON p.admission_number = h.admission_number
WHERE note LIKE '%blood pressure%' OR note LIKE '%Discharged%';

gets you:

fullname = Bob
admission_number = 3456

fullname = Lucy
admission_number = 9001

fullname = Mary
admission_number = 7828

And running the following query:

SELECT DISTINCT p.fullname, p.admission_number FROM patient p
INNER JOIN history h ON p.admission_number = h.admission_number
WHERE note LIKE '%blood pressure%';

gets you:

fullname = Bob
admission_number = 3456

Upvotes: 1

Related Questions