Re: Can not workout how to do this!
Hi,
It would be easy to find patients who missed ANY 3 appointments...
select id_patient, count(*) as missed from appointments
where arrived is null group by id_patient having missed>=3;
To find the LAST 3 appointments, you need to use a subselect or GROUP_CONCAT (both available in 4.1). I'm sure there are other ways.
TRY this
create table tmp(id int, date int, arr int);
insert into tmp values (1,1,NULL), (1,2,NULL), (1,3,3),(1,4,NULL), (2,1,NULL), (2,2,2), (2,3,3), (2,4,5);
1. Use a group_concat function to list arrived and order by date. Convert NULLS to zeros using ifnull.
select id, group_concat(ifnull(arr,0) order by date) as appt_order from tmp group by 1;
+------+------------+
| id | appt_order |
+------+------------+
| 1 | 0,0,3,0 |
| 2 | 0,2,3,5 |
+------+------------+
Now just find ids where the appt_order starts with 0,0,0 (i just used 2 missed appts here).
select id from tmp group by id having group_concat(ifnull(arr,0) order by date) like '0,0,%';
+------+
| id |
+------+
| 1 |
+------+
2. You could also use subselects (and if you have 5.0 you could make this a VIEW). Now just check where first, second and third appts are NULL
select t1.id, (select arr from tmp t2 where t1.id=t2.id order by date limit 0,1) as first,
(select arr from tmp t3 where t1.id=t3.id order by date limit 1,1) as second,
(select arr from tmp t4 where t1.id=t4.id order by date limit 2,1) as third
from tmp t1 group by t1.id;
+------+-------+--------+-------+
| id | first | second | third |
+------+-------+--------+-------+
| 1 | NULL | NULL | 3 |
| 2 | NULL | 2 | 3 |
+------+-------+--------+-------+
Chris