MySQL: How to return empty record when all fields are NULL?

Question

Situation 1:

SELECT SUM(mark) AS totalMark, userID, credit
FROM users, marks
WHERE marks.receiverID = users.userID
AND userID = '2'

It will return

totalMark   userID  credit
---------------------------
0           2       0.0

And it is very good, no problem. But here is the situation 2:

SELECT SUM(mark) AS totalMark, userID, credit
FROM users, marks
WHERE marks.receiverID = users.userID
AND userID = '-1'

Result:

totalMark   userID  credit
---------------------------
NULL        NULL    NULL

But what I want is return nothing (no record found). Anyone could help me?

Anytime you use an aggregate you will get at least one row.

Kermit
– Kermit

2013-03-07 18:32:45 +00:00
Commented Mar 7, 2013 at 18:32 — Kermit
– Kermit, Commented Mar 7, 2013 at 18:32

Bill Karwin · Accepted Answer · 2013-03-07 18:32:08Z

2

When you use an aggregate function like SUM(), the query is bound to return a row. But since it's summing no values, the result of the sum is undefined.

You could work around this in the following way:

SELECT * FROM (
    SELECT SUM(mark) AS totalMark, userID, credit
    FROM users, marks
    WHERE marks.receiverID = users.userID
    AND userID = '-1'
) t
WHERE t.totalMark IS NOT NULL;

answered Mar 7, 2013 at 18:32

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

Collectives™ on Stack Overflow

MySQL: How to return empty record when all fields are NULL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related