Lorenzo,
Try this:
select P.ssn
from patientrecall_table as P
left join appointment_table as A
on P.ssn = A.ssn
and appdate between '2004-04-15' and '2004-04-30'
where P.nrd >= current_date
and A.ssn is null
Before applying the where clause, the left join will have
- a row for each appointment in the given range
- a row for each patient who doesn't have an appointment
Then the where clause throws away the ones with appointments.
regards,
Bill
> From: "lorenzo.kh" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Subject: how to rewrite this query without using temporary table
> Date: Thu, 15 Apr 2004 12:03:04 +0800
> 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=20
> 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=3Db.ssn
> where a.nrd>=3Dcurrent_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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
