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
:)
