Left Outer Join doesn’t return all rows from my left table?

by

Nanne’s answer given explains why you don’t get the desired result (your WHERE clause removes rows), but not how to fix it.

The solution is to change WHERE to AND so that the condition is part of the join condition, not a filter applied after the join:

SELECT day.days, COUNT(*) as opens 
FROM day 
LEFT OUTER JOIN tracking
ON day.days = DAY(FROM_UNIXTIME(open_date)) 
AND tracking.open_id = 10 
GROUP BY day.days

Now all rows in the left table will be present in the result.

More Related Contents:

  1. Get a list of dates between two dates
  2. How to populate a table with a range of dates?
  3. MySQL Query GROUP BY day / month / year
  4. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  5. Error in MySQL when setting default value for DATE or DATETIME
  6. Difference between two dates in MySQL
  7. sql join two table
  8. Get the latest date from grouped MySQL data
  9. How to select the most recent set of dated records from a mysql table
  10. MySQL: How do I join same table multiple times?
  11. Compare only day and month with date field in mysql
  12. Postgres LEFT JOIN with WHERE condition
  13. Insert/ Update random date in MySQL
  14. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  15. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  16. How do I extract Month and Year in a MySQL date and compare them?
  17. SQL select only rows with max value on a column [duplicate]
  18. How do I query between two dates using MySQL?
  19. Mysql: Select all data between two dates
  20. Can a table field contain a hyphen?
  21. Select mysql query between date?
  22. MySQL JOIN the most recent row only?
  23. Search all columns of a table using a single where condition with single keyword in mysql
  24. What does SQL clause “GROUP BY 1” mean?
  25. Syntax error when using except in a query
  26. Why the order of evaluation for expressions involving user variables is undefined?
  27. sql like operator on integer
  28. MySQL – SELECT * INTO OUTFILE LOCAL ?
  29. The best way to remove value from SET field?
  30. SQL query with avg and group by

Leave a Comment