MySQL Forums
Forum List  »  Newbie

Re: Can not workout how to do this!
Posted by: Chris Stubben
Date: April 27, 2005 08:42AM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Can not workout how to do this!
April 27, 2005 08:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.