Re: sub-queries
SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - 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 - 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
Re: Maximum JOINED tables
And how long does it take to compile a 32-tables-statement? With the gupta server a 14-tables-statement took 2 hours. Sabine Weaver, Walt wrote: Hmmm... we're running 3.23.47 here and have run into a 32 table join limit... --Walt -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 10:19 AM To: mysql mailing list Cc: Arthur Fuller Subject: Re: Maximum JOINED tables * Arthur Fuller I have searched the manual without success. I want to know the maximum number of tables MySQL permits to be JOINed in a single SELECT statement. According to crashme, it is 32 in 3.22.39 and 63 in 3.23.39. URL: http://www.mysql.com/information/crash-me.php -- Roger - 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 - 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 - 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
[Fwd: mysql query question]
Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Taylor, as far as I see from the documentation and own trials you can just delete from table where conditions_of_this_table. So you can not delete values matching a temp table. But I think I have a possible workaround for you: 1: create a new table with the opposite of your question, i.e. the rows both tables have in common: -- create table interim select table1.* from table1, table2 where table1.ticker_name = table2.ticker_name; 2. delete table1 3. rename table interim to table1 I think that will do what you want. Bye Sabine Taylor Lewick wrote: How can I delete from a table the results of a join query.. I.e, join two tables together, get the resutls, now want to delete that data from one of the tables... Do I have to create a tempory table, hold the data, and delete from the one table data matching in the temp table, or can I just combine a delete statement with my original query...? here is my query, which works.. select distinct table1.ticker_name from table1 LEFT JOIN on table2 on (table1.ticker_name = table2.ticker_name) where table2.ticker_name IS NULL; Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. 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 - 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
[Fwd: Add existing related data to a table]
Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Alexander, your problem is near to Taylor Lewick's and I have a similar suggestion for you: 1) create newtab select tableA.*, tableB.SubcatID from tableA.*, tableB where ... 2) delete tablea 3) rename table newtab to tablea If there are more rows in tableB (1:n), you don't loose data. The data of tabA will be dublicated. Ok, that are 3 statements. Bye Sabine Alexander Shaw wrote: Hi, I'm looking for some suggestions of making a one time update to some of my tables to make them useable with new forms and queries I'm creating. I have a series of tables which hold related data and only the last part of the data is stored in a final table, which means to search data based on some of the information higher up the table structure is making some very complex and dodgy queries. I want to insert the related data into the final table but can't think of a way of doing it. The example below might help me explain better what I mean. Consider two tables - TableA which contains PicID and KeywordID TableB which contains SubcatID and KeywordID What I want to do is insert the corresponding values for relating to KeywordID for SubCatID into a new column in TableA. Any suggestions how to work this to add the extra information to existing records? Alex -- Alexander Shaw Agricultural Stock and Assignment Photography p.s. to get throught he spam trap - sql query --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 - 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 - 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
[Fwd: Joining same table twice for different rows -- possible?]
Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hi Ron, I suspect users.id and userid1 / userid2 have to be joined? Then: select your_desired_cols from users u1, users u2, log where u1.id=log.userid1 and u2.id=log.userid2; Bye Sabine Ron Jamison wrote: Hi, Consider the following schema: Database.Users: id username Database.Log: id userid1 userid2 entry If I'm SELECT'ing on the Log table and I want to pull usernames out of Users table for both users (which will most likely be unique) is there a specific JOIN for this? Forgive me if I've missed something in the manual pages; I've always had to do two SELECTs and thus have been looking for a quick[er] solution to this for some time now. Thank you in advance! Ron Jamison (moderators: although I've looked at the manual page plenty of times before, http://www.mysql.com/doc/manual.php?search_query=join today produced the following: Search results Did you mean jan? The search for 'join' produced no results -- kind of ironic) - 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
substitute for sub query with group function
Hello, I just want to get the value of one column in the row where max of another col of this table is. With the possibility of sub queries I would write: select col1 from table t1 where col2 = (select max(col2) from tab1e t2); But how to do it without a sub query? Just with 1 statement? I find no solution. Not wanting to believe there is none Sabine - 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
Re: substitute for sub query with group function
Hello Philip, that was my first choice, too. But no, error : Invalid use of group function and select col1, max(col2) from table; is error 1140: mixing of group cols with non groupü cols is illegal (That would other sql backends say too) and other variants of the above like select col1 from table t1, table t2 where =max(col2) result in the same error reports Perhaps someone else has an idea? Best regards Sabine Philip Spradling wrote: I could be missing something, but what about: select col1 from t1 where col2 = max(col2); On Sun, 12 May 2002 23:04:14 +0200 Sabine Richter [EMAIL PROTECTED] wrote: Hello, I just want to get the value of one column in the row where max of another col of this table is. With the possibility of sub queries I would write: select col1 from table t1 where col2 = (select max(col2) from tab1e t2); But how to do it without a sub query? Just with 1 statement? I find no solution. Not wanting to believe there is none Sabine - 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 - 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 - 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
Using MyODBC with Python
Hello, I can't think straight today. So please help me: I downloaded and installed myODBC 3.51. As far as I understand, it is only the driver, which provides the connection to the database. In my example, it is MySQL 3.23.49. The manual says that in my application I have to allocate the environment handle, set the version, allocate the connection handle, conect to the server etc.. But don't I have to have a library / module which provides functions which I can use to do all that? But where is it? Or do I have to write all that by myself? I don't know where to begin. Please, may anybody help me understand the architecture and how to use myODBC with Python? Perhaps anybody has a code snippet for me? I have to be productive with that till tomorrow morning. So it is urgent for me. Thanks a lot for your help. Sabine - 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
Can't start mysql-service on Win XP
Hello, for the first time I installed Mysql 3.23.49 on win XP professional. I installed it with admin privileges, installed the service -so far everything seemed to be ok. But when I tried to start the service, it didn't start (failure number 1067). I don't have any idea, what that failure number means, where to find something about it (who raised it: win or mysql?) and where to search for the reasons. I already searched for this failure in the mysql ref manual and in the win help system. I'll be grateful for any tip! Thanks in advance Sabine Richter - 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
Re: Can't start mysql-service on Win XP
Hi Jan, I suppose you already tried this: http://www.mysql.com/doc/N/T/NT_start.html I did! This could be problems w/ your path or your my.ini (overwritten my mysqladmin). mysqld-nt --help has the correct basedir (.\mysql\) Oh, what's that? In my.ini I just wrote basedir=mysql -without the last backslash! Can that be a reason? And how to change it? The same for datadir! telnet localhost 3306 says: Verbindungsaufbau zu localhost... Es konnte keine Verbindung mit dem Host hergestellt werden, auf Port 3306. Es konnte nicht, da der Zielcomputer die Verbindung verweigerte. (perhaps incorrect translation: connection to localhost on port 3306 wasn't possible. The computer didn't allow the connection) If it won't start anyway, put mysqladmin.exe to your autostart. I don't understand that. I thought that mysqladmin requires an already started server service. Isn't that right? However, I tried start mysqladmin/host=localhost It says that it needs mysqld running and tells me to check it with telnet. What I did. See above. I have no debug-version of mysqld-nt. Any other idea?? best regards Sabine - 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
[Fwd: How get the columns that are between an interval?]
oops, i've just sent it to mike. Sabine Richter wrote: Hello Mike, hello Silvia, I think, the statement you suggest, Mike, isn't right. It gives you only patients who are over 1 year in hospital. That means, who came before (or at) 2000-01-25 and left after (or at) 2001-01-30. I think, you didn't mean that, Silvia? I understood, you wanted the persons who came to hospital between 2000-01-25 and 2001-01-30 and that it doesn't matter when they left. That will be: SELECT * FROM table WHERE begin_date/admission_date =2000-01-25 and begin_date/admission_date = 2001-01-30; I'm not familiar with mysql this moment, but perhaps, if admission_date is a timestamp, you have to extract the dateportion of the column. Admission date can be 2001-01-30-13h:23m:45s and that may be interpreted as greater than 2001-01-30. (I don't know if that's a problem in mysql, in other backends it is). Best regards Sabine mike wrote: Dra. Silvia Andreasi, SELECT * FROM table WHERE begin_date_column =2000-01-25 and 2001-01-30= end_date_column; begin_date_column and end_date_column have to be date type columns. Mike sql,mysql Dra. Silvia Andreasi wrote: Hi, How can I get the columns between a given time interval? Something like: The patients admitted between january 25, 2000 and january 30, 2001 ?? I'm new to MySql and would like a suggestion from you... Best Regards Silvia - 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
