ciprian ciprian - 6 months ago 10
SQL Question

mysql - Query by interval and or returning nothing

I need to select only emails from the users table where the

weekly_mail
(datetime) is older that one week or null and if the user's points add up to more than 10. It's not returning anything now.

points_plus table structure

id | user_id | points | date
1 62 5 2015-05-13 08:42:37
2 62 15 2015-05-14 03:12:32


query

$q = "SELECT u.email, u.weekly_mail, SUM(p.points) AS points
FROM users u
LEFT JOIN points_plus p
ON u.id = p.user_id
WHERE points > 10 AND u.weekly_mail < NOW() - INTERVAL 1 WEEK OR u.weekly_mail = NULL
GROUP BY p.user_id";
$result = $this->db->mysqli->query($q);

if (!$result) {
printf("Query failed: %s\n", $this->db->mysqli->error);
exit;
}

$rows = array();
while($row = $result->fetch_row()) {
$rows[]=$row;
}

$result->close();
return $rows;

Answer

Check this:

$q = "SELECT u.email, u.weekly_mail, SUM(p.points) AS points
      FROM users u
      LEFT JOIN points_plus p
      ON u.id = p.user_id
      WHERE u.weekly_mail < (NOW() - INTERVAL 1 WEEK) OR u.weekly_mail IS NULL
      GROUP BY p.user_id having SUM(p.points) > 10";