Return 0 when no rows [modified]

[Heinz_]

Hi, how can i return a 0 if my query returns no results? I'm trying to do this because i have a subquery inside a query but if the subquery returns no values then the main query also returns nothing. I'm using MySQL 5. Thanks in advance. -- modified at 20:24 Saturday 15th July, 2006

[ra ra ra ra]

sounds like you are using an inner join between your queries? try a left or right join. or post you sql and you may get a better response

[Heinz_]

Hi, you're right, there's a inner join inside my query, check it out, hope you understand the query and the problem: select SUM((t1.valor + t1.total) - t2sum) from consultas as t1 inner join (select consulta, SUM(cantidad_pagada) as t2sum from pagos_consultas where fecha_pago='2006-07-05' group by consulta) as t2 on t2.consulta=t1.id where t1.nula=0 and t1.pagada=0 and t1.fecha='2006-07-05' I had no idea about left and right join so i went to http://www.w3schools.com/sql/sql\_join.asp and took a read, the left join is the one for me! but this will return NULL in the second table when there're no matches and if you look at my query in "(t1.valor + t1.total) - t2sum" i do a " - " operation and i cant reduce a number with a null value or can i? i must replace that null for a 0. Thanx man. -- modified at 15:42 Monday 17th July, 2006

[Ennis Ray Lynch Jr]

isnull( field, newValue) works in SQL Server and NVL in Oracle. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane

[ra ra ra ra]

you can use also use the coalesce() function to remove the Null value. eg. replace - t2sum with - coalesce(t2sum,0) is a nice little function is coalesce as you can list as many variables as you want, it works through from left to right until it finds a non null value and uses that. sounds like its the same as the isNull() function mentioned in previous post. its a SQL server only function though! -- modified at 4:23 Tuesday 18th July, 2006

[Heinz_]

Thanx man. You really did it! thanx. I was reading mysql 5 manual and there's a ISNULL(expression) function but it returns 1 if the expression is null and 0 if not null (the inverse of what i need), but, thank god there's also a IS NOT NULL wich return 1 when not null and 0 when null, so i have now many ways to acomplish my objective. [ADDED] Reading about COALESCE() in mysql 5, and it works in a different manner than the normal ISNULL for MS SQL SERVER and others...COALESCE does not replace any null values nor it has a fixed return value, COALESCE() in mysql takes a list of values as a parameter and returns the first Not NULL value and returns NULL if any of the values in the list are not null, ie: mysql> SELECT COALESCE(NULL,NULL,1,'hello','world',25); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL So, all i have to do in my case is: COALESCE(mycolumnvalue,0) and if the query returns mycolumnvalue null then it will return 0. Bye, -- modified at 14:52 Tuesday 18th July, 2006