Re: Problem with query
Naz, That query logic runs without a error on the server I have to hand (5.0.37), but it has three issues: (i) unless there is an exceptionless 1:1 relationship between group_post_mod_option.option_id and group_post_mod_option.option_name, results for the latter column will be meaningless because it is not aggregated. Absent a 1:1 relationship, you need a subquery to fetch the name. (ii) the WHERE condition group_post_moderation.group_post_id = 37 will remove all NULL rows from the right side of the join, turning the OUTER JOIN effectively into an INNER JOIN, so you might as well write it as an INNER JOIN (iii) why not use the alias `count` in the ORDER BY clause (the query engine would likely spot that)? So that would give something like... SELECT o.option_id, (SELECT option_name FROM group_post_mod_option.option_name WHERE option_id = o.option_id) AS name, COUNT(m.group_post_moderation_option) AS count FROM group_post_mod_option o JOIN group_post_moderation m ON o.option_id = m.group_post_moderation_option AND m.group_post_id = 37 GROUP BY o.option_id ORDER BY count, o.option_name; PB - Naz Gassiep wrote: I am running this query: SELECT group_post_mod_option.option_id, group_post_mod_option.option_name, COUNT(group_post_moderation.group_post_moderation_option) AS count FROM group_post_mod_option LEFT OUTER JOIN group_post_moderation ON (group_post_mod_option.option_id = group_post_moderation.group_post_moderation_option AND group_post_moderation.group_post_id = 37) GROUP BY group_post_mod_option.option_id ORDER BY COUNT(group_post_moderation.group_post_moderation_option), group_post_mod_option.option_name; And getting this error: 'Invalid use of group function' Without the ORDER BY clause, the query works just fine. Anyone know what's going on? Regards, - Naz. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Is 9640 a word by itself? A full-text search wouldn't find abc9640, you'd need to use LIKE for that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Don O'Neil [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 2:44 AM To: mysql@lists.mysql.com Subject: Problem with query on 5.11 Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? I'm using MySQL 5.1.11. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Yes, there is a full text index, there are about 12,000 rows or so. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
I just deleted and re-created my indexes and the query works now... I guess the index got corrupted somehow. Strange that I never saw any indications of this in messages, just that 0 rows were returned. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query
From: Jon Miller [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Problem with query Date: Tue, 24 May 2005 19:40:32 +0800 Quite new to MySQl and queries in general and would like some help in the following query: select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate =2005-05-09 It generates a listing that has years from 2001 to present. All I'm looking for is information start from 2005-05-09 to present. Thanks TEXT.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Try putting quotes around the date select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate = '2005-05-09' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query and password
Hello.
Looks like passwords in your table are stored in old short format.
Use old_password() instead of password(). See:
http://dev.mysql.com/doc/mysql/en/password-hashing.html
Lancer Emotion 16 [EMAIL PROTECTED] wrote:
Hello everbody,i have a problem with mysql and i wish you could help me.
I have this table named users :
+++--+--+---+-+-
+
| ID | user | pass | thegroup | firstname | surname |
enabled
|
+++--+--+---+-+-
+
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
| 2 | admin2 | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
+++--+--+---+-+-
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
select * from users where pass='admin';
select * from users where pass=password('admin');
i get an empty set, i dont know why.
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
Please help me.
Thanks
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query and password
Lancer Emotion 16 wrote:
I have this table named users :
+
| ID | user | pass | thegroup | firstname | surname | enabled
+++--+--+---+-+-
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
that one should work, except...
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
You wouldn't maybe guess that your pass field above is too small? :-)
FWIW,
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with query
Luiz Rafael Culik Guimaraes wrote: Dear Friends Running the follow query select max( sr_recno) from test_table down work for re the error id returned by mysql_errno is 3731792 server is 4.1.1a-alpha Table structure is SR_DELETEC char(1) SR_RECNO bigint(15) PRI auto_increment VALUE double(18,6) OBS text ENABLE tinyint(4) DATE_LIM date days double(6) percent double(8,2) descr varchar(50) cardid char(1) code_id varchar(8) Does it work from command line client? If yes, double-check your client code to make sure it has no bugs. If not, it would be a bug in MySQL itself, and you need to prepare a test case for a bug report. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query comparing to a DATE on Linux OpenOffice
On Tue, 2003-11-11 at 00:15, Alaios wrote: what a front-end is? And what OO1.1.0 provides? MySQL is a database server and a front-end is another program that you use to get to that server and use the database. OpenOffice is a complete office software package (word processor, spreadsheet, ...) that also has a database interface. You can check it out at www.openoffice.org -Marc Marc [EMAIL PROTECTED] wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query comparing to a DATE on Linux OpenOffice
what a front-end is? And what OO1.1.0 provides? Marc [EMAIL PROTECTED] wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: problem with query
Leonardo Javier Bel? wrote: Hi ALL! I have a problem with this query, because it keeps failing and I dont know why (it says that the concat statement is wrong but there is nothing on the online docs...) select st.id, concat(st.required), st.name from states st, agenda ag left outer join ag.id=concat(AGE,st.required) where st.type='AGE' and st.id0; Did you mean: select st.id, concat(st.required), st.name from states st left join agenda ag on ag.id=concat(AGE,st.required) where st.type='AGE' and st.id0; in this st.required is an int(7) and ag.id is a varchar(40). Thanks Leo. Mysql sql query select, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Query based on HTML form values - Clarification
You have it right. But if the user searches for new power boats, and doesn't specify a make, make should not appear in your WHERE clause. The query has to be different depending on the criteria that have been chosen (or not chosen, as the case may be). Prior to executing the query, look at all the form fields you've gotten and ignore the ones that don't have a value attached to them. -bill web-dev wrote: Bill Lovett wrote: Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. If I use AND, wouldn't all conditions need to be true to return a result? In the case of the search form as constructed, the user can select or enter search criteria information in from one to all of the available search criteria. Using the truncated criteria list as an example, if the user selected 1)new 2) power boats and left the 3) make text box empty, AND, and the whole query, fails because the WHERE make like '$make' condition returns false, I think. Please correct me if I'm wrong! I want and need to understand this.. - 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: Problem with Query based on HTML form values
Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. SELECT * FROM boat WHERE condition='$condition' AND type='$type' Or you might try SELECT * FROM boat WHERE (condition='new' OR condition='used') AND type='sail' You probably don't need to use LIKE since the form fields will always supply you with the same values. -bill web-dev wrote: Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - 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: Problem with Query based on HTML form values - Clarification
Bill Lovett wrote: Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. If I use AND, wouldn't all conditions need to be true to return a result? In the case of the search form as constructed, the user can select or enter search criteria information in from one to all of the available search criteria. Using the truncated criteria list as an example, if the user selected 1)new 2) power boats and left the 3) make text box empty, AND, and the whole query, fails because the WHERE make like '$make' condition returns false, I think. Please correct me if I'm wrong! I want and need to understand this.. SELECT * FROM boat WHERE condition='$condition' AND type='$type' Or you might try SELECT * FROM boat WHERE (condition='new' OR condition='used') AND type='sail' You probably don't need to use LIKE since the form fields will always supply you with the same values. -bill web-dev wrote: Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - 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
Re: Problem with query - join two tables - order by goes crazy
Since it can't use an index, and has to sort the output, what exactly is unexpected? Norris, Joseph wrote: Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. 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 - 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: Problem with query
select * from MEMBERS,FEE_PAYMENTS where FEE_PAYMENTS.PAID = '0' and MEMBERS. MemberID = FEE_PAYMENTS. MemberID; assuming you have a field PAID, with values 0 or 1,or even YES or NO cheers Mark I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [EMAIL PROTECTED] - - 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: Problem with query
Depends on how the fee paymnents are defined. At its simplest: SELECT Members..Name FROM Members WHERE NOT(Members.MemberID = FEE_PAYMENTS.MenmbersID) would select members that have no fee payment records. You could build on it from there... For the mySQL gurus, sorry about the syntax guys, I've been with Access to long but I'm coming over quickly :-) Pierre Torkil Johnsen wrote: I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [EMAIL PROTECTED] - - 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 -- Pierre du Parté Final Filer Software 349 Worrigee Road Worrigee, NSW, Australia 2540 http://www.finalfiler.com Phone 61 2 44216374 Mobile 0413 483 066 If it feels good, do it! - 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: Problem with query
I don't see any code or errors. [EMAIL PROTECTED] wrote: Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. Somebody help me? - 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: Problem with query
What kind of code ? I typically send each of those in as separate queries. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 01, 2002 8:36 AM Subject: Problem with query Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. Somebody help me? - 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: Problem with query
At 10:36 -0300 2/1/02, [EMAIL PROTECTED] wrote: Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. If you're using some kind of MySQL API within a program that you're writing you can't issue multiple statements within the same row. Issue three separate statements (and don't include the semicolons). Somebody help me? - 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: Problem with query
Hi, Getting non-empty results when comparing something with NULL seems very odd to me too. Even if it is undefined, id expect to see an error message telling me i shouldnt compare an undefined variable rather than receiving results. Why not ask someone to change things so that WHERE xx=NULL gives the same results as WHERE xx IS NULL. Or would that be an unusual thing to request. Andrew Murphy -Original Message- From: jim barchuk [mailto:[EMAIL PROTECTED]] Sent: 27 October 2001 8:32 pm To: Paul DuBois Cc: [EMAIL PROTECTED] Subject: Re: Problem with query Hi Paul! On Sat, 27 Oct 2001, Paul DuBois wrote: At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. I think WHERE x = NULL 'may' return non-empty results, or not, simply because NULL is undefined and most bets are off for that query. Undefined doesn't mean random. Yes, http://www.mysql.com/doc/W/o/Working_with_NULL.html does say In MySQL, 0 or NULL means false and anything else means true. In this case I take undefined to mean that what appears to work this time may not work next time, depending on the structure of the query, or even if it works consistently with one version of MySQL it may not with another. Quoting page 47 of your book, If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined. Similarly, quoting http://www.mysql.com/doc/P/r/Problems_with_NULL.html , To look for NULL values, you must use the IS NULL test. 'Must' is a very strong word and I take it as gospel. I think I have less problem with trying to understand what accounts for something that may appear odd to others is that I don't try to think about 'why' when given such clear instrutions. I had a -very- educational experience with NULL with my very first attempt at MySQL -- HTML rendering. TABLE cells that -appeared- to contain something but shouldn't have and really didn't. Had my head spinning for hours until I finally got a grip on the idea that NULL is a Very Strange Thing and that I *must* deal with it only in certain ways. NULL rocks. :) Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] - 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: Problem with query
Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. sql, database, damned crap, grrr... - 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: Problem with query
Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. Both 0 and are 'not nothing.' They are distinct values within their set of allowed values. NULL is a 'different' kind of value that is very very useful in many cases. For a field that is allowed to contain NULL: A query that returns NULL means essentially 'do nothing with these results.' A query that returns 0 or , without -further- tests for those values, means 'do something with these values.' When coding it's easier to 'do nothing' rather than test further and later decide you really need to 'not do something.' :) As I just posted to another message, read through the hits returned by http://www.mysql.com/doc/manual.php?search_query=null . As described there dealing with NULL requires a 'different' kind of thinking. It's like thinking about black holes or Heisenberg's box or /dev/null or other places that don't really 'exist' in our normal human plane of reference *but* do in fact exist and can be used. Have a :) day! jb ob-filter-words: database table sql -- jim barchuk [EMAIL PROTECTED] - 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: Problem with query
At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. jb ob-filter-words: database table sql -- jim barchuk [EMAIL PROTECTED] - 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: Problem with query
Hi Paul! On Sat, 27 Oct 2001, Paul DuBois wrote: At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. I think WHERE x = NULL 'may' return non-empty results, or not, simply because NULL is undefined and most bets are off for that query. Undefined doesn't mean random. Yes, http://www.mysql.com/doc/W/o/Working_with_NULL.html does say In MySQL, 0 or NULL means false and anything else means true. In this case I take undefined to mean that what appears to work this time may not work next time, depending on the structure of the query, or even if it works consistently with one version of MySQL it may not with another. Quoting page 47 of your book, If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined. Similarly, quoting http://www.mysql.com/doc/P/r/Problems_with_NULL.html , To look for NULL values, you must use the IS NULL test. 'Must' is a very strong word and I take it as gospel. I think I have less problem with trying to understand what accounts for something that may appear odd to others is that I don't try to think about 'why' when given such clear instrutions. I had a -very- educational experience with NULL with my very first attempt at MySQL -- HTML rendering. TABLE cells that -appeared- to contain something but shouldn't have and really didn't. Had my head spinning for hours until I finally got a grip on the idea that NULL is a Very Strange Thing and that I *must* deal with it only in certain ways. NULL rocks. :) Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] - 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: Problem with query
hi. first of many replies, i'm sure: mysql select count(compid) from dbLastFaxed where lastFaxedDateTime IS null ; when you're looking for NULL fields, select where something IS NULL, instead of '=' -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: October 26, 2001 5:46 PM To: [EMAIL PROTECTED] Subject: Problem with query I am having a strange problem with a query... I have a table that contains 3 columns and 3,315,599 rows. One of the columns is a date time field. Out of 3,315,599 rows 1,555,157 contain an entry in the date time field and the other 1,760,442 rows contain null in the date time field Here is the problem I will illustrate it with queries and results. - 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: Problem with query
Yours is the first and the winner! :) Thanks using is null worked in the query... Do you know why = doesn't work? -Original Message- From: Ravi Raman [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 4:00 PM To: Mysql; Brendin Subject: RE: Problem with query hi. first of many replies, i'm sure: mysql select count(compid) from dbLastFaxed where lastFaxedDateTime IS null ; when you're looking for NULL fields, select where something IS NULL, instead of '=' -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: October 26, 2001 5:46 PM To: [EMAIL PROTECTED] Subject: Problem with query I am having a strange problem with a query... I have a table that contains 3 columns and 3,315,599 rows. One of the columns is a date time field. Out of 3,315,599 rows 1,555,157 contain an entry in the date time field and the other 1,760,442 rows contain null in the date time field Here is the problem I will illustrate it with queries and results. - 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: Problem with query
At 15:54 -0600 10/26/01, Brendin wrote: Yours is the first and the winner! :) Thanks using is null worked in the query... Do you know why = doesn't work? NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) - 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
