Re: detect rows that has zero values

2004-11-19 Thread denys 
Louie Miranda a écrit :
Im trying not to list the rows that has zero values.
this is my current select statement:
select id,country_city from rates_ocean order by country_city
The fields that i have to detect are:
fieldvalue1
fieldvalue2
fieldvalue3
how can i do this on mysql, ex:
select country from rates (if fieldvalue1,fieldvalue2,fieldvalue3 = 0
(do not display)) order by country;
 

SELECT  country
FROM Rates
WHERE fieldvalue1  0 AND fieldvalue2  0 AND fieldvalue3  0
ORDER BY country
is it what you want ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: detect rows that has zero values

2004-11-19 Thread Gleb Paharenko 
Hello



You may use query like this:

 select country from rates where (fieldvalue1 !=0) and (fieldvalue2 !=0) 

 and (fieldvalue3 !=0) order by country;



See:

  http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html



 

 Louie Miranda [EMAIL PROTECTED] wrote:

 Im trying not to list the rows that has zero values.

 

 this is my current select statement:

 

 select id,country_city from rates_ocean order by country_city

 

 The fields that i have to detect are:

 

 fieldvalue1

 fieldvalue2

 fieldvalue3

 

 how can i do this on mysql, ex:

 

 select country from rates (if fieldvalue1,fieldvalue2,fieldvalue3 = 0

 (do not display)) order by country;

 



-- 
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: detect rows that has zero values

2004-11-19 Thread Michael Stassen 
denys wrote:
 SELECT  country
 FROM Rates
 WHERE fieldvalue1  0 AND fieldvalue2  0 AND fieldvalue3  0
 ORDER BY country
 is it what you want ?

Gleb Paharenko wrote:
Hello
You may use query like this:
 select country from rates where (fieldvalue1 !=0) and (fieldvalue2 !=0) 
 and (fieldvalue3 !=0) order by country;

See:
  http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
And since 0 is false and every other number is true, this could be 
simplified to

  SELECT country
  FROM Rates
  WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
  ORDER BY country;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: detect rows that has zero values

2004-11-19 Thread SGreen 
Michael, 

As was just discussed in this list, non-numeric strings values  will also 
evaluate to FALSE but if that string starts with a number, it could be 
TRUE or FALSE (depending on the actual value of the numeric portion of the 
string). 

He never explicitly said if those were numerical fields or not (though it 
is reasonable to assume they are because he is comparing them to 0 and not 
'0'). So your solution is more likely than not a shorthand way of 
representing a valid solution to this particular query, I must caution the 
newer readers of this list against using this technique with non-numeric 
fields as your answers may not agree with your expectations.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Michael Stassen [EMAIL PROTECTED] wrote on 11/19/2004 10:02:19 
AM:

 denys wrote:
 
   SELECT  country
   FROM Rates
   WHERE fieldvalue1  0 AND fieldvalue2  0 AND fieldvalue3  0
   ORDER BY country
   is it what you want ?
  
 
 Gleb Paharenko wrote:
 
  Hello
  
  You may use query like this:
   select country from rates where (fieldvalue1 !=0) and (fieldvalue2 
!=0) 
   and (fieldvalue3 !=0) order by country;
  
  See:
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
 
 And since 0 is false and every other number is true, this could be 
 simplified to
 
SELECT country
FROM Rates
WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
ORDER BY country;
 
 Michael
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: detect rows that has zero values

2004-11-19 Thread Bulant Denys 
 --- Michael Stassen [EMAIL PROTECTED] a
écrit : 
 denys wrote:
 
   SELECT  country
   FROM Rates
   WHERE fieldvalue1  0 AND fieldvalue2  0 AND
 fieldvalue3  0
   ORDER BY country
   is it what you want ?
  
 
 Gleb Paharenko wrote:
 
  Hello
  
  You may use query like this:
   select country from rates where (fieldvalue1 !=0)
 and (fieldvalue2 !=0) 
   and (fieldvalue3 !=0) order by country;
  
  See:
   

http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
 
 And since 0 is false and every other number is true,
 this could be 
 simplified to
 
SELECT country
FROM Rates
WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
ORDER BY country;
 
 Michael
 
 

I will go to bed less fool this evening... Is it just
a clearest way to write it, or is there some
performance issues too ?






Vous manquez d’espace pour stocker vos mails ? 
Yahoo! Mail vous offre GRATUITEMENT 100 Mo !
Créez votre Yahoo! Mail sur http://fr.benefits.yahoo.com/

Le nouveau Yahoo! Messenger est arrivé ! Découvrez toutes les nouveautés pour 
dialoguer instantanément avec vos amis. A télécharger gratuitement sur 
http://fr.messenger.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: detect rows that has zero values

2004-11-19 Thread Michael Stassen 
Good point.
Michael
[EMAIL PROTECTED] wrote:
Michael, 

As was just discussed in this list, non-numeric strings values  will also 
evaluate to FALSE but if that string starts with a number, it could be 
TRUE or FALSE (depending on the actual value of the numeric portion of the 
string). 

He never explicitly said if those were numerical fields or not (though it 
is reasonable to assume they are because he is comparing them to 0 and not 
'0'). So your solution is more likely than not a shorthand way of 
representing a valid solution to this particular query, I must caution the 
newer readers of this list against using this technique with non-numeric 
fields as your answers may not agree with your expectations.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael Stassen [EMAIL PROTECTED] wrote on 11/19/2004 10:02:19 
AM:


denys wrote:
 SELECT  country
 FROM Rates
 WHERE fieldvalue1  0 AND fieldvalue2  0 AND fieldvalue3  0
 ORDER BY country
 is it what you want ?

Gleb Paharenko wrote:

Hello
You may use query like this:
select country from rates where (fieldvalue1 !=0) and (fieldvalue2 
!=0) 

and (fieldvalue3 !=0) order by country;
See:
 http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
And since 0 is false and every other number is true, this could be 
simplified to

  SELECT country
  FROM Rates
  WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
  ORDER BY country;
Michael
--
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: detect rows that has zero values

2004-11-19 Thread Michael Stassen 

Bulant Denys wrote:
 --- Michael Stassen [EMAIL PROTECTED] a
écrit : 

denys wrote:
 SELECT  country
 FROM Rates
 WHERE fieldvalue1  0 AND fieldvalue2  0 AND
fieldvalue3  0
 ORDER BY country
 is it what you want ?

Gleb Paharenko wrote:

Hello
You may use query like this:
select country from rates where (fieldvalue1 !=0)
and (fieldvalue2 !=0) 

and (fieldvalue3 !=0) order by country;
See:

http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
And since 0 is false and every other number is true,
this could be 
simplified to

  SELECT country
  FROM Rates
  WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
  ORDER BY country;
Michael
I will go to bed less fool this evening... Is it just
a clearest way to write it, or is there some
performance issues too ?
In theory, fieldvalueN != 0 causes a comparison operation which will 
return 1 or 0 (true or false) for each field, then the results are combined 
with ANDs, which would be 3 extra comparisons compared to simply combining 
with ANDs.  In practice, I'd bet the optimizer is smarter than that, and the 
performance will be identical.  Even if the extra comparison were not 
optimized away, I doubt it would appreciably affect the speed.  You could 
certainly try it both ways with your data to see if if you get any 
difference, but I don't expect any.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: detect rows that has zero values

2004-11-19 Thread Martijn Tonies 

  And since 0 is false and every other number is true,
  this could be
  simplified to
 
 SELECT country
 FROM Rates
 WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
 ORDER BY country;
 
  Michael
 
 

 I will go to bed less fool this evening... Is it just
 a clearest way to write it, or is there some
 performance issues too ?

Actually -- this isn't clear at all. It's the shortest
way, not the clearest.

It's most probably not portable too.

One might think that fieldvalue1 is a boolean
column instead of a numeric one.

Personally, I would advise against such a query
it were to be coded for re-use or use in an application.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]