Hello!
I am having this small problem. It’s not really a problem its just that I don’t know how to do this, but I’m sure it can be done easily if you know how…
This is a little app I am writing where user can ask quiz questions and other users then submit answers. Now I am coding the page that will display statistics for questions like how many people answered, how many correct ,how many incorrect. etc…
Anyway, I have a query that uses a left join
This means that sometimes not all values are available and thus retuned as NULL
This causes some problems later on in my script when I send the array as json encoded object and then my javascript expects numeric values and when it gets null, some of the sorting of data fails
Here is my query
SELECT Q.id as qid,
Q.qbody as qb,
Q.qtype as qt,
Q.difflevel as dl,
UNIX_TIMESTAMP(Q.sent_on) as ts,
QS.count_answers as ca,
QS.count_correct as cc,
QS.count_incorrect as ci,
UNIX_TIMESTAMP(QS.last_modified) as lts
FROM QUESTIONS as Q
LEFT JOIN QUESTIONS_STATS as QS on QS.questions_id = Q.id
WHERE Q.users_id = 4
order by qid DESC limit 1000
No all record in QUESTIONS have records in QUESTIONS_STATS because not all questions have had any answers submitted yet.
What I need is for the query to return 0 for values that otherwise return NULL
for example, QS.count_correct should return 0 if there are no records found.
the same for QS.count_incorrect and UNIX_TIMESTAMP(QS.last_modified) as lts
What is the best way to cast null to 0 in this case?