Get columns from two tabels with fallback value

Question

I'm currently trying to fetch an object from one table with global stats including it's daily stats from a second table. For this I use the following join-query that works intended as long as the statsTable contains a matching row for both 'oID' and 'Date' columns.

SELECT
  o.ID as ID,
  o.Image as Image,
  o.Text as `Text`,
  o.Views as `Views`,
  o.Clicks as Clicks,
  COALESCE(s.Views, 0) as DayViews,
  COALESCE(s.Clicks, 0) as DayClicks
FROM objectTable o
LEFT JOIN statsTable s
ON o.ID = s.oID
WHERE o.userID = 1
  AND DATEDIFF(CURDATE(), s.Date) < 1
LIMIT 1

How do I have to change the query to get a result row even if statsTable does not contain a matching row?

The query does not give errors, I just do not want it to return an empty result as I know for sure that there are data in objectTable.

Answer 1

You just need to move the condition on the s table into the ON clause:

SELECT o.ID as ID, o.Image as Image, o.Text as `Text`,
       o.Views as `Views`, o.Clicks as Clicks,
       COALESCE(s.Views, 0) as DayViews,
       COALESCE(s.Clicks, 0) as DayClicks
FROM objectTable o LEFT JOIN
     statsTable s
     ON o.ID = s.oID AND DATEDIFF(CURDATE(), s.Date) < 1
WHERE o.userID = 1 ;

A LEFT JOIN keeps all rows in the first table, regardless of whether the ON clause evaluates to true or not. The columns in the second table are NULL for non-matching rows -- which the WHERE clause then filters out.

As a result, conditions on the first table should be in the WHERE clause. Conditions on subsequent tables should be in the ON clause -- for a LEFT JOIN.