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

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

CobraStrikes@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;

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

Aggro wrote:
> CobraStrikes@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
:)