Re: The = operator
I'm sorry for the rant, but nulls in databases make me see red! NULLS ARE GARBAGE! You are using a 'NULL-safe equal to operator', meaning, WHERE NOT group_id = 3, and oh, by the way, I DON'T WANT ANY NULLS TO QUALIFY AS 3. And yes this will be much slower, because now we can't use the index on group_id. Null is not an initial value, it is really saying that nothing has ever been put into this field, item, or what-ever. It is uninitialized and no one can say exactly what is in it, this is why it is marked as null. Using the null-safe equal to operator seems wrong, of course too me having any nulls in your data is wrong, or just plain dirty data, and I wouldn't put any faith into the results from a database that contains nulls, because the output is unpredictable if it is not very carefully coded. You (everyone in the SQL world) would be better off using the IS NULL and IS NOT NULL operators and the IFNULL() function to find all null values in their data, and initialize them to the appropriate initial value. This may require discussions with the application designers or project managers. Whom ever is responsible for these null values being your data all the frecking time? It is people that don't really understand what a null value is, and who also have the responsibility of designing a database application. So people like me who write the code need to add 'IS NOT NULL' to every conditional statement in our logic. What you want to do noted in your email below should be very simple, but only if you have CLEAN DATA. I would clean or what we call scrub your data first, then you can execute a simple and very fast select statement. Use a temporary table if you need to, just don't try to process dirty data, the end results will only make you look bad to anyone looking at the results. My $0.02, Mike. Morten wrote: Hi, I have a query where I want to retrieve all the people who are not in a specific group. Like so: SELECT id, name FROM people WHERE group_id != 3; Since people may not be in a group at all, I also need to test if the column is NULL: SELECT id, name FROM people WHERE group_id != 3 OR group_id IS NULL; Running that through EXPLAIN things look fine, but if I instead use: SELECT id, name FROM people WHERE NOT group_id = 3; I get a full table scan. Is that because is = equivalent to a function in a sense? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: union operator problems in MySQL v3.23?
glenn Union is 4.0 feature. david Notwithstanding end-of-life status, is there an issue in MySQL v3.23 r.e. the union operator? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union operator problems in MySQL v3.23?
On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
Notwithstanding end-of-life status, is there an issue in MySQL v3.23
r.e. the union operator?
My installation returns a syntax error on any query containing the union
operator, even with the sample queries provided in the documentation:
mysql SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
ERROR 1064: syntax error near 'UNION SELECT REPEAT('b',10)' at line 1
Support for UNION was not added until 4.0.
Jim Winstead
MySQL Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union operator problems in MySQL v3.23?
Jim Winstead wrote, On 1/11/2008 12:54 PM:
On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
Notwithstanding end-of-life status, is there an issue in MySQL v3.23
r.e. the union operator?
My installation returns a syntax error on any query containing the union
operator, even with the sample queries provided in the documentation:
mysql SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
ERROR 1064: syntax error near 'UNION SELECT REPEAT('b',10)' at line 1
Support for UNION was not added until 4.0.
Jim Winstead
MySQL Inc.
Thanks, Jim and David. I guess it's about time to upgrade!
--
Glenn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not operator in Select statements
On 10/24/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... Jasper Bryant-Greene Thank you Jasper. I remember from the manual that this _should_be correct. But when it doesn't work I am inclinded to ask. I will go try to track down the cause of my disappointment with the query. Thank you. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/332/mccartney_paul.php McCartney, Paul Song Lyrics
Re: Not operator in Select statements
On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repetition-operator operand invalid
Hello. Use 5.0.4. Please, could you send an example of query (with pattern) for words with sensitive characters which worked in 5.0.2 and doesn't work in 5.0.4? Scott Klarenbach [EMAIL PROTECTED] wrote: I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in one of my regular expression queries. The expression works like this: a query for 'search' returns true for a matching 'search' field, but, querying 's$$#e%ar^c)(h' must also return true for a 'search' field. In other words, I need to pad every letter of the search string and tell it to allow any number of non-alphanumeric characters. Here is the expression I'm using below, for the term SEARCH: '[^a-zA-Z0-9]*S[^a-zA-Z0-9]*E[^a-zA-Z0-9]*A[^a-zA-Z0-9]*R[^a-zA-Z0-9]*C[^a-= zA-Z0-9]*H[^a-zA-Z0-9]*' As I said, it worked fine until I upgraded. Is it to do with the double parsing MySQL does with REGEXP's? In some instances, I get emtpy result set where I used to get a match, and in other instances, I get a 'repetition-operator operand invalid'. Is there a simple way I can prevent errors from a search string that contains ^*$ or other sensitive expression characters? addslashes() in php maybe? Any help is appreciated. -- 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: IN operator
* Oliver Hirschi [EMAIL PROTECTED] [09/13/04]: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? The MySQL version 4.1.x now support sub-queries, if that is what you mean by 'inner-selects', so you could also select the values used for IN. ... WHERE fpObjectID IN (SELECT id FROM id_table WHERE ...) I don't think the number of values is limited, at least using a sub-query, however I don't know for sure. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN operator
Since you do not seem to have subqueries available to you (pre 4.1.x), have you looked at modifying your queries to use JOINs, temp tables, or both? What is it you are trying to accomplish, we can help you to build the appropriate statement(s). Optionally, you can upgrade to a version that supports subqueries (4.1+). Shawn Green Database Administrator Unimin Corporation - Spruce Pine news [EMAIL PROTECTED] wrote on 09/13/2004 05:29:28 AM: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? Thanks Regards -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN operator
Hi there, I used very likely statement last week to update one table. My IN value is around 20. I checked the manual and there is nothing about any limitation on IN values. However, when i was running it, it worked very well sometimes, while sometimes, the query crashed in the middle and i have to REPAIR table. I've not figure out the reason of the crash yet. but i think you should be aware of it. Monet --- Oliver Hirschi [EMAIL PROTECTED] wrote: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? Thanks Regards -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IN operator
IN is a fantastic operator, but there are some limitations especially memory wise. Check out this algorithm Say you're using an Integer with an average of 8 digits, i.e. in the 100s millions, now you send an in list of say 20 of these 8 digits numbers. Since the data is passed to mysql as a string, the parser has to allocate memory for 160 bytes (20 * 8 bytes) + 19 bytes for each comma. 339 bytes BAH that's nothing right? Well, these are bytes allocated outside of a key buffer, thus if your key buffer is set to 1.9 GB on a 32 bit system, your application has many of these IN list passed to it, mysql will crash because it just hit the 2 GB limit. Does this explain your issue, no not necessarily but it's good to add and might explain some weird experiences. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Monet [mailto:[EMAIL PROTECTED] Sent: Monday, September 13, 2004 10:22 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: IN operator Hi there, I used very likely statement last week to update one table. My IN value is around 20. I checked the manual and there is nothing about any limitation on IN values. However, when i was running it, it worked very well sometimes, while sometimes, the query crashed in the middle and i have to REPAIR table. I've not figure out the reason of the crash yet. but i think you should be aware of it. Monet --- Oliver Hirschi [EMAIL PROTECTED] wrote: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? Thanks Regards -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- 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: IN operator
Great explanison. I guest this is the point. after each time the table crashed during such process, it always followed a error message saying something about the memory violation. Thanks a lot. your idea also open my mind and deeper my understanding about mysql. Monet --- Dathan Vance Pattishall [EMAIL PROTECTED] wrote: IN is a fantastic operator, but there are some limitations especially memory wise. Check out this algorithm Say you're using an Integer with an average of 8 digits, i.e. in the 100s millions, now you send an in list of say 20 of these 8 digits numbers. Since the data is passed to mysql as a string, the parser has to allocate memory for 160 bytes (20 * 8 bytes) + 19 bytes for each comma. 339 bytes BAH that's nothing right? Well, these are bytes allocated outside of a key buffer, thus if your key buffer is set to 1.9 GB on a 32 bit system, your application has many of these IN list passed to it, mysql will crash because it just hit the 2 GB limit. Does this explain your issue, no not necessarily but it's good to add and might explain some weird experiences. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Monet [mailto:[EMAIL PROTECTED] Sent: Monday, September 13, 2004 10:22 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: IN operator Hi there, I used very likely statement last week to update one table. My IN value is around 20. I checked the manual and there is nothing about any limitation on IN values. However, when i was running it, it worked very well sometimes, while sometimes, the query crashed in the middle and i have to REPAIR table. I've not figure out the reason of the crash yet. but i think you should be aware of it. Monet --- Oliver Hirschi [EMAIL PROTECTED] wrote: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? Thanks Regards -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- 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] ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
Craig- are you sure you want to test ranking_id '10' ??? -martin - Original Message - From: Craig Hoffman [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Friday, July 09, 2004 3:51 PM Subject: Re: Between Operator Thanks everyone for helping out.I took Michael's advice and made a new table called ranking and two columns. It defiantly cleared some things up but I am still having issues using the BETWEEN operator. I just need to pull up everything BETWEEEN 10 and 18 and it keeps adding additional rows. Suggestions? What am I doing wrong? Here is my query: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id '10' AND ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Thanks, Craig On Jul 9, 2004, at 1:17 PM, Pete Harlan wrote: On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c ... SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * from routes, users where area = '$area' and style = '$style' and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
Craig Hoffman wrote: This should pull up all the rock climbs that are in Yosemite, that are traditional style and are between the rating 5.5 to 5.10c. Here is my query: SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; Not sure what those square brackets are doing there, but your main problem is that MySQL has no way of knowing what order you think those rating strings should be in. The string '5.5' is greater than '5.10c', and the number 5.5 is greater than 5.10. I think you're going to need to change the way you represent the ratings -- maybe something like '5.05' and '5.10c' would work (depending on what other possibilities are, and how the letters are supposed to affect sorting). Then manipulate the strings to produce what you're used to when it comes time to display them (that, or have two columns: one for display and one for sorting). -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
On Fri, 09 Jul 2004 11:38:05 -0400, Keith Ivey [EMAIL PROTECTED] wrote: Craig Hoffman wrote: This should pull up all the rock climbs that are in Yosemite, that are traditional style and are between the rating 5.5 to 5.10c. Here is my query: SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; Not sure what those square brackets are doing there, but your main problem is that MySQL has no way of knowing what order you think those rating strings should be in. The string '5.5' is greater than '5.10c', and the number 5.5 is greater than 5.10. I think you're going to need to change the way you represent the ratings -- maybe something like '5.05' and '5.10c' would work (depending on what other possibilities are, and how the letters are supposed to affect sorting). Then manipulate the strings to produce what you're used to when it comes time to display them (that, or have two columns: one for display and one for sorting). You could also set up a linked table that holds the sorting and rating string information. You could go with either a two column or three column table. The only difference being that the three column table would have an auto increment primary key so the sorting column can be changed without affecting the linking. A simple join connects the sort and rating columns. Michael -- Michael Johnson [EMAIL PROTECTED] Internet Application Programmer, Pitsco, Inc. +++ Opinions are my own, not my employer's +++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c ... SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * fromroutes, users where area = '$area' and style = '$style'and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
Thanks everyone for helping out.I took Michael's advice and made a new table called ranking and two columns. It defiantly cleared some things up but I am still having issues using the BETWEEN operator. I just need to pull up everything BETWEEEN 10 and 18 and it keeps adding additional rows. Suggestions? What am I doing wrong? Here is my query: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id '10' AND ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Thanks, Craig On Jul 9, 2004, at 1:17 PM, Pete Harlan wrote: On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c ... SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * fromroutes, users where area = '$area' and style = '$style'and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- 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: Between Operator
At 05:51 PM 7/9/2004, you wrote: Thanks everyone for helping out.I took Michael's advice and made a new table called ranking and two columns. It defiantly cleared some things up but I am still having issues using the BETWEEN operator. I just need to pull up everything BETWEEEN 10 and 18 and it keeps adding additional rows. Suggestions? What am I doing wrong? Here is my query: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id '10' AND ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Craig, Did you read the MySQL manual? http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html has examples that will show you how the Between operator works. SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id '10' AND ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Your Between clause is being evaluated incorrectly because the two operands are being equated to gibberish. Here's what I think it is doing. I added parenthesis to try and demonstrate what MySQL is interpreting it as: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = (ranking.rating BETWEEN (ranking.id '10') AND (ranking.id = '18')) AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Are you sure you need this: ranking.id = ranking.rating ??? Basically I think all you need is:WHERE ranking.rating BETWEEN '10' AND '18' So it would look like this: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.rating BETWEEN '10' AND '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC; You can of course put the SQL statement on more than one line so you can read it better. I'm assuming Ranking.Rating is a Char or VarChar otherwise remove the quotes around '10' and '18'. Mike On Jul 9, 2004, at 1:17 PM, Pete Harlan wrote: On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c ... SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * fromroutes, users where area = '$area' and style = '$style'and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
Hi Paul, Using the following fixed things. However, I am surprised at why it is not working in 4.0.18 (Win32)? Previously I had 4.0.17 (Win32) and things work perfectly. I guess you are right, it's a bug. [mysqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE Thanks. Hassan Paul DuBois wrote: At 12:50 -0600 2/23/04, Paul DuBois wrote: At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're using MySQL 4.x. The ansi shortcut was added in 4.1.1: http://www.mysql.com/doc/en/Server_SQL_mode.html That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- 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: Concat. operator ||
I get the same thing on our system (MySQL 4.0.15 running on Linux Mandrake
9.1).
According to the manual, section 1.8.4:
MySQL Server understands the || and operators to mean logical OR and
AND, as in the C programming language. In MySQL Server, || and OR are
synonyms, as are and AND. Because of this nice syntax, MySQL Server
doesn't support the standard SQL-99 || operator for string concatenation;
use CONCAT() instead. Because CONCAT() takes any number of arguments, it's
easy to convert use of the || operator to MySQL Server.
If you rewrite your query as follows, you should get the result you wanted:
select concat('This is a ', 'test string');
Rhino
- Original Message -
From: Hassan Shaikh [EMAIL PROTECTED]
To: MySQL (General List) [EMAIL PROTECTED]
Sent: Monday, February 23, 2004 7:58 AM
Subject: Concat. operator ||
Hi All,
I am running MySQL in ANSI standard mode. However the following
statement is giving unexpected result. Am I missing something?
mysql select 'This is a' || ' test string';
+---+
| 'This is a' || ' test string' |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
Here's a part of my my.ini, indicating that I am in ANSI mode.
# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable= sql-mode=ansi
set-variable= key_buffer=32M
set-variable= max_allowed_packet=1M
set-variable= table_cache=32
set-variable= net_buffer_length=8K
set-variable= sort_buffer=1M
set-variable= record_buffer=512K
set-variable= myisam_sort_buffer_size=4M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
log-bin
server-id= 1
Thanks.
Hassan
--
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: Concat. operator ||
Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Concat. operator ||
At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
At 12:50 -0600 2/23/04, Paul DuBois wrote: At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're using MySQL 4.x. The ansi shortcut was added in 4.1.1: http://www.mysql.com/doc/en/Server_SQL_mode.html That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
At 17:20 -0600 2/23/04, Paul DuBois wrote: At 12:50 -0600 2/23/04, Paul DuBois wrote: At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're using MySQL 4.x. The ansi shortcut was added in 4.1.1: http://www.mysql.com/doc/en/Server_SQL_mode.html Er, that should be: at least not if you're using MYSQL 4.0.x. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exponentiation operator
On Wed, Oct 08, 2003 at 11:16:03AM +0200, Bob Brands wrote: What is the correct syntax for using a Exponentiation operator in MySQL? It's not an operator but a function. Look at EXP and POW in the docs: http://www.mysql.com/doc/en/Mathematical_functions.html Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exponentiation operator
I think exp(number, number) will do the job. Check the manual for the correct syntax. Thanks Emery - Original Message - From: Bob Brands [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 11:16 Subject: Exponentiation operator What is the correct syntax for using a Exponentiation operator in MySQL? BTW is this even posible? bye, Bob http://www.beheervisie.nl/disclaimer. -- 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: truncation operator
Hi. Don't know whether you got any answer yet, but I did not see any. On Tue, Nov 06, 2001 at 11:01:55AM -, [EMAIL PROTECTED] wrote: Hi folks... I've been using the + and the - operators to which seem to work fine in 4.0 but cannot seem to work out what to do with the *. A truncation operator would be of great help, can somebody tell me how to use it in a MATCH... AGAINST query please? thanks Chris MATCH ... AGAINST is going to supports the following boolean operators: * +word means the that word must be present in every row returned. * -word means the that word must not be present in every row returned. * and can be used to decrease and increase word weight in the query. * ~ can be used to assign a negative weight to a noise word. * * is a truncation operator. Well, as far as I understand, word will match only word, not e.g. wording, whereas work* will match both word and wording. I did not try it, though. If that doesn't work for you, could you please elaborate. Bye, Benjamin. -- [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
