Return 0 if no row/empty result in mysql

Question

is there a way where I can return 0 if i get an empty result in mysql?

SELECT t1.amount as Amt
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.project_id = t2.id

WHERE t2.proj_name = "Project_Kalinga"
AND t1.date_sent = (SELECT MAX(date_sent) FROM table WHERE project_id = "0123")

Currently for the project name 'Project Kalinga' I get an empty result.

Is there a way where from this query even if i get an empty result, I'll just get 0 instead? So then the output would be like

| Amt |
|  0  |

Thank You!

Here's the sqlfiddle

http://sqlfiddle.com/#!2/0c4c5/2

Conrad Frix · Accepted Answer · 2014-04-01 18:12:11Z

4

You can

create a "Dummy row" with zero
do a left join to your main query.
Coalesce the real value and the dummy value

SELECT Coalesce(t1.amt, d.amount) AS amt 
FROM   (SELECT 0 as amount
        FROM   DUAL) AS d
       LEFT JOIN  
       (SELECT t1.amount AS Amt 
        FROM   table1 AS t1 
               INNER JOIN table2 AS t2 
                       ON t1.project_id = t2.id 
        WHERE  t2.proj_name = "project_kalinga" 
               AND t1.date_sent = (SELECT Max(date_sent) 
                                   FROM   table1 
                                   WHERE  project_id = "0123")) AS t1 
       ON 1 = 1

Demo

edited Apr 1, 2014 at 18:12

answered Apr 1, 2014 at 17:27

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hearmeroar Over a year ago

nevermind, got it. Thanks! just changed t1.amount to t1.Amt

Conrad Frix Over a year ago

@hearmeroar I also used "table" when I should have used "table1" Thanks for the fiddle btw. It makes everything eaiser

Raging Bull · Accepted Answer · 2014-04-01 17:33:41Z

2

Try this:

SELECT IFNULL(t1.amount,0) as Amt
FROM table t1
LEFT OUTER JOIN table2 t2
ON t1.project_id = t2.id AND t2.proj_name = "Project_Kalinga"
WHERE t1.date_sent = (SELECT MAX(date_sent) FROM table WHERE project_id = "0123")

edited Apr 1, 2014 at 17:33

answered Apr 1, 2014 at 17:19

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

3 Comments

hearmeroar Over a year ago

It didn't work for me either. Still getting the error MySQL returned an empty result set and it didn't return any rows

Raging Bull Over a year ago

@hearmeroar: Can you show me some contents of the table, please?

hearmeroar Over a year ago

still didn't work. Here is the requested content sqlfiddle.com/#!2/0c4c5/2

Collectives™ on Stack Overflow

Return 0 if no row/empty result in mysql

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related