MySQL SELECT subquery

Question

I have a calendar and user_result table and I need to join these two queries.

calendar query

SELECT `week`, `date`, `time`, COUNT(*) as count
FROM `calendar` 
WHERE `week` = 1
GROUP BY `date`
ORDER BY `date` DESC

and the result is

{"week":"1","date":"2014-08-21","time":"15:30:00","count":"4"}, {"week":"1","date":"2014-08-20","time":"17:30:00","count":"12"}

user_result query

SELECT `date`, SUM(`point`) as score
FROM `user_result` 
WHERE `user_id` = 1
AND `date` = '2014-08-20'

and the result is just score 3

My goal is to always show calendar even if the user isn't present in the user_result table, but if he is, SUM his points for that day where calendar.date = user_result.date. Result should be:

{"week":"1","date":"2014-08-21","time":"15:30:00","count":"4","score":"3"}, {"week":"1","date":"2014-08-20","time":"17:30:00","count":"12","score":"0"}

I have tried this query below, but the result is just one row and unexpected count

SELECT c.`week`, c.`date`, c.`time`, COUNT(*) as count, SUM(p.`point`) as score
FROM `calendar` c
INNER JOIN `user_result` p ON c.`date` = p.`date`
WHERE c.`week` = 1
AND p.`user_id` = 1
GROUP BY c.`date`
ORDER BY c.`date` DESC

{"week":"1","date":"2014-08-20","time":"17:30:00","count":"4","score":"9"}

SQL Fiddle

Answer 1

I put a pre-aggreate query / group by date as a select for the one person you were interested in... then did a left-join to it. Also, your column names of week, date and time (IMO) are poor choice column names as they can appear to be too close to reserved keywords in MySQL. They are not, but could be confusing..

SELECT
      c.week,
      c.date,
      c.time,
      coalesce( OnePerson.PointEntries, 0 ) as count,
      coalesce( OnePerson.totPoints, 0 ) as score
   FROM
      calendar c
         LEFT JOIN ( select
                           r.week,
                           r.date,
                           COUNT(*) as PointEntries,
                           SUM( r.point ) as totPoints 
                        from
                           result r
                        where
                               r.week = 1
                           AND r.user_id = 1
                        group by
                           r.week,
                           r.date ) OnePerson
            ON  c.week = OnePerson.week
            AND c.date = OnePerson.date
   WHERE 
      c.week = 1
   GROUP BY 
      c.date 
   ORDER BY 
      c.date DESC 

Posted code to SQLFiddle

Answer 2

ow sorry, i was edited, and i was try at your sqlfiddle, if you want to show all date from calendar you can use LEFT JOIN, but if you want to show just the same date between calendar and result you can use INNER JOIN, note: in this case INNER JOIN just show 1 result, and LEFT JOIN show 2 results

    SELECT c.`week`, p.user_id, c.`date`, c.`time`, COUNT(*) as count, p.score 
    FROM `calendar` c
    LEFT JOIN 
    (
      SELECT `date`, SUM(`point`) score, user_id
      FROM `result` 
      group by `date`
    ) p ON c.`date` = p.`date`

    WHERE c.`week` = 1
    GROUP BY c.`date`
    ORDER BY c.`date` DESC