how to rewrite this query without using temporary table
Hi, I have 2 tables.patientrecall_table and appointment_table.The patientrecall_table used to store what is the nextrecall date for the patient while the appointment_tablewill 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 thantoday date AND they don't have appointment made between thedate 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 aleft join tmpA as bon a.ssn=b.ssnwhere 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 | | | -00-00 | |+---++--+-++---+2 rows in set (0.00 sec) mysql> desc appointment_table;+-+-+--+-++---+| Field | Type | Null | Key | Default | Extra |+-+-+--+-++---+| appdate | date | | | -00-00 | || ssn | varchar(9) | | | | || remark | varchar(50) | | | | |+-+-+--+-++---+3 rows in set (0.00 sec) Server version: 4.0.18-nt //***
regular backup database in mysql
I am using MySql version 4.0.0-alpha in Windows 2000. Everytime when I want to backup the database,i do it in MS-DOS. Using this command: >mysqldump --opt mydatabasename > mybackup.sql Now,lets say i never shut down my pc and the MySql is running all the time. How can i do a regular backup (ie.every evening 9.00 pm) automatically? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select * into outfile
Hi, i am using mysql 4.0.0 version -alpha in windows 2000. i'd like to export the mysql table data to excel format. So,i use the outfile method. mysql>select * into outfile 'backup.xls' from ; When i open 'backup.xls' in microsoft excel,i can view all the data ,but i can't view the field name. Is there any method to export the field name together when i use outfile method? Tq. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql query(group by + order by)
Hi, I got a table named patient_treatment_history Below is some of the records inside the table. patient_id,treatment_date,charges 1014,2002-01-28,20 1001,2002-02-02,100 1026,2002-04-08,74 1001,2002-04-15,85 1014,2002-05-05,50 1030,2002-05-16,125 1030,2002-06-18,180 1001,2002-06-25,125 I try to retrieve the last visit date for each patient using this query: select patient_id,treatment_date from patient_treatment_history group by(patient_id) order by treatment_date desc. But the result is not what i expected. Can anybody assist me on this? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql + win 2000 + command prompt
hi, I had installed mysql 4.0 in win 2000. I used Command Prompt to connect to mysql and do all the sql query there. It is often/normal that we can make mistake in the typing. When i encounter this typing error,the mysql just like 'hang' it there. In order to solve this,the only way i know is press "Ctrl + c" key to abort,and re-connect again. But is there any other method instead of pressing "Ctrl+c" key? Below is the example output: mysql> desc mytable; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | aaa | varchar(20) | YES | | NULL| | | bbb | varchar(20) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> select * from mytable; Empty set (0.00 sec) mysql> select * from mytable' ( I key in ' instead of ; ) '> '> ? '> what the key should i use to reset this? '> '> '> '> '> Thank You - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to get the message that a table is currently locked?
Hi, Assuming that there are 2 users for MYSQL. First user issues 'lock tables customer_table write'; Second user wish to view the customer info using the command 'select * from customer_table'; This select query will be pending untill the first user issues the 'unlock tables' command. I am wondering is there any method that i can get the message something like 'This customet_table is currently being locked by xxx(first user name)' while the select query is pending. And display this message to the second user. Can it be done? Thank You - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Locking + Timeout
Hi, I have a question regard the table locking. Let say there is a table ,mytablename type=MyISAM in my database. And lets say there are 2 users who are currently log in. First user locks a table write: mysql> lock tables mytablename write; Query OK, 0 rows affected (0.00 sec) Then,second user wishes to query something from mytablename. mysql>select * from mytablename; ...(pending or in a queue)... I know that the second query will be pending untill the 1-st user issues the "unlock tables" command. So,i am wondering is it the second query will be waiting there 'forever' if the 1-st user 'never' issue the "unlock tables" command? Or is there any method maybe like 'waiting_timeout' that I can set.If the waiting time > 'variable' second,then the query will automatically be deleted Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
myisamchk: Can't unlock file (Errcode: 11)
Hi, I am using mysql 4.0.0-aplha on win 2000 professional. I install mysql in c:\mysql. I write a script ( customerscript.txt) and i save this script in c:\mysql\bin. The contents of customerscript.txt I will paste at the bottom of this mail. Then,I run my script at the command prompt. C:\mysql\bin>mysql < customerscript.txt And later on I try to check the table using the command below. And I get the error and warning. C:\mysql\bin>myisamchk ../data/test/customer Checking MyISAM file: ../data/test/customer Data records: 1 Deleted blocks: 0 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: Can't unlock file (Errcode: 11) - check record links myisamchk: error: 11 when locking indexfile '../data/test/customer' Can anybody tell me what actually is this error? ** customerscript.txt ** use test; create table customer( id integer not null auto_increment primary key, name varchar(20), address varchar(150) ); insert into customer (id,name,address) values (null,'Good customer','Good customer address'); quit ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show processlist
Hi, I am developing a web-based application using java + tomcat + mysql in Win 2000. When the application is running,I try to view the processlist in MS-DOS using the commad "show processlist". mysql> show processlist; +--+--+-++-+ --+---++ | Id | User | Host| db | Command | Time | State | Info| +--+--+-++-+ --+---+ + | 4369 | ODBC | localhost | release2 | Query | 0 | NULL | show processlist | | 7916 | root | localhost | release2 | Query | 0 | freeing items | insert into app_overflow(location_id, date, status, time, ic, assistant_id, provider_id, reserve_sta | | 7917 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | | 8334 | unauthenticated user | localhost | NULL | Connect | NULL | Reading from net | NULL| +--+--+-++-+ --+---+---+ Actually,my application is running without error,but I'd like to find out the "meaning" of this kind of message.(freeing items,Reading from net,unauthenticated user and etc). Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
