Mysql data entries older then MAX(date)

I have two tables, one with devices and on with corresponding service times. Each device can have several services. I'm looking for d开发者_运维技巧evices which last service is older than 3 Months. I tried this, which of course didn't work:

SELECT devices.id, 
       devices.name,  
       services.servicetime 
FROM devices
    LEFT JOIN services 
        ON services.device_id = devices.id
WHERE MAX(services.servicetime) < DATE_SUB(NOW(), INTERVAL 3 MONTH);

How con i get the desired result?

---

Add GROUP BY devices.id to your query, and change the condition to a HAVING clause (evaluated after grouping). You want the MAX(services.servicetime) per device, where your current result set includes one row per service.

SELECT
  devices.id,
  devices.name,
  MAX(services.servicetime)
FROM
  devices
LEFT OUTER JOIN
  services
ON
  services.device_id = devices.id
GROUP BY
  devices.id
HAVING
  MAX(services.servicetime) < CURRENT_TIMESTAMP - INTERVAL 3 MONTH

If you want to include devices that have never been serviced, then add OR MAX(services.servicetime) IS NULL to the end.

---

SELECT devices.id, devices.name, MAX(services.servicetime)
FROM 
    devices
    LEFT JOIN
    services ON services.device_id=devices.id
GROUP BY
    devices.id, devices.name
HAVING
    MAX(services.servicetime) < DATE_SUB(NOW(), INTERVAL 3 MONTH)
    OR
    MAX(services.servicetime) IS NULL;

---

select devices.id,devices.name.services.servicetime from devices,services
where services.id = devices.id and services.id in 
(select services.id from services where
 DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP by services.id)

---

GROUP BY and HAVING is your clause:

SELECT devices.id, devices.name, max(services.servicetime) FROM devices
LEFT JOIN services ON services.device_id=devices.id
GROUP BY devices.id, devices.name
HAVING MAX(services.servicetime) < DATE_SUB(NOW(), INTERVAL 3 MONTH);