|
Hi,
I have 2 tables.
patientrecall_table
and appointment_table.
The patientrecall_table used to store what is the
next
recall date for the patient while the appointment_table
will store
all the appointments made.
Now, I'd like to get through this:
"Patients
Due for Recall, but No Scheduled Appointment between certain date."
In other
words, I will need to:
select those patients who their next recall date
greater than
today date AND they don't have appointment made between
the
date from and date to.
Currently, what I did was using the temporary
table approach.And it was working fine.
create temporary table tmpA(ssn varchar(9) not
null);
insert into tmpA select ssn from appointment_table
where (appdate
between '2004-04-15' and '2004-04-30') group by ssn;
select a.ssn from
patientrecall_table as a
left join tmpA as b
on a.ssn=b.ssn
where
a.nrd>=current_date and b.ssn is null;
drop table tmpA;
Is there any other ways that I can use instead
of using the temporary table? Please advise.
Thank you.
//***********************************************
mysql> desc
patientrecall_table;
+-------+------------+------+-----+------------+-------+
|
Field | Type | Null | Key |
Default | Extra
|
+-------+------------+------+-----+------------+-------+
|
ssn | varchar(9) | | PRI
|
| |
| nrd |
date |
| | 0000-00-00 |
|
+-------+------------+------+-----+------------+-------+
2 rows in set
(0.00 sec)
mysql> desc
appointment_table;
+---------+-------------+------+-----+------------+-------+
|
Field | Type | Null | Key
| Default | Extra
|
+---------+-------------+------+-----+------------+-------+
| appdate |
date |
| | 0000-00-00 |
|
| ssn | varchar(9)
| |
|
| |
| remark | varchar(50)
| |
|
|
|
+---------+-------------+------+-----+------------+-------+
3 rows in
set (0.00 sec)
Server
version: 4.0.18-nt
//***********************************************
| 