469,622 Members | 1,831 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

How can I return a row when the condition is not met ?

Hi All,
How can I return a row when the condition is not met for example simplified

SELECT Name,SUM(Cost) AS T FROM DB WHERE Name="+Name +" GROUP BY Name

this works fine but I ned it to return Name and Zero Cost if Name does not exist
May 26 '06 #1
2 2012
Co**********@al.com wrote:
How can I return a row when the condition is not met for example simplified
Rows are not returned when conditions are not met. That is the basic
idea behind databases. If you want the "other" rows to be returned, you
need to change your conditions.
SELECT Name,SUM(Cost) AS T FROM DB WHERE Name="+Name +" GROUP BY Name

this works fine but I ned it to return Name and Zero Cost if Name does not exist


If Name doesn't exist, how can you return Name? Or do you mean that you
want to return all rows, but if Name is not equal to "+Name +" you want
Cost to be 0?

If so, then just use the if-sentence in your query.
select Name,if(Name='+Name +',sum(Cost),0) from x group by Name;
May 26 '06 #2
Aggro wrote:
Co**********@al.com wrote:
How can I return a row when the condition is not met for
example simplified

Rows are not returned when conditions are not met. That is the basic
idea behind databases. If you want the "other" rows to be returned, you
need to change your conditions.
SELECT Name,SUM(Cost) AS T FROM DB WHERE Name="+Name +" GROUP BY Name

this works fine but I ned it to return Name and Zero Cost if Name does
not exist

If Name doesn't exist, how can you return Name? Or do you mean that you
want to return all rows, but if Name is not equal to "+Name +" you want
Cost to be 0?

If so, then just use the if-sentence in your query.
select Name,if(Name='+Name +',sum(Cost),0) from x group by Name;


if you are using a "where-clause" that specifies col=value, then you will always
get NO ROWS RETURNED if "value" does not exist. Period. you cannot select that
which does not exist. Something must be returned to compare against for the "if"
statements to evaluate.

Now, to get around that issue you will need to handle it in your program.
psuedo-code :
select x from db where x='somevalue';
if rows = 0 then echo "'somevalue' Cost = 0"

again, when using a where clause - you cannot sum something that does not exist.

case and point (from an SQL92 compliant "real" database engine:

select '5',a, case when a is null then '0' else sum(c) end
from testa where a=5 group by a;
0 rows selected

the other poster obviously never tested any of the syntax using the where
clause. That might work if and only if you returned all rows NOT EQUAL to
'Name' but will not work using an EQUALS evaluation.

Maybe if you described the problem you are tring to solve a bit better, then
there could be an alternative solution.
--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)
Jun 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

94 posts views Thread by John Bailo | last post: by
27 posts views Thread by Maximus | last post: by
7 posts views Thread by hugo27 | last post: by
13 posts views Thread by cppquester | last post: by
6 posts views Thread by exander77 | last post: by
12 posts views Thread by Matt B | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.