Reputation: 153
i need help with a mysql query. My tables:
objects
+---------+--------+
| id | name |
+---------+--------+
| 1 | house 1|
| 2 | house 2|
| 3 | house 3|
+---------+--------+
objects_expire
+----------+-----------+
| object_id| expire |
+----------+-----------+
| 1 | 2014-09-11|
| 1 | 2015-09-11|
| 2 | 2014-09-11|
| 2 | 2015-09-11|
| 2 | 2016-09-11|
| 3 | 2013-09-11|
| 3 | 2014-09-11|
| 3 | 2015-09-15|
+----------+-----------+
Now i need objects where max 'expire' is bigger then 2015-09-04 and smaller then 2015-09-18 (+/- 7 days)
Like this result:
+----------+-----------+-----------+
| object_id| expire | name |
+----------+-----------+-----------+
| 1 | 2015-09-11| house 1 |
| 3 | 2015-09-15| house 3 |
+----------+-----------+-----------+
This is what i have now:
SELECT o.id, MAX(uio.expire) AS object_expires
FROM objects AS o
LEFT JOIN objects_expire AS oe ON oe.object_id = o.id
WHERE expire < '2015-09-18'
AND expires > '2015-09-04'
GROUP BY o.id
But thats not correct.
Thanks for any help!!!
Upvotes: 1
Views: 3589
Reputation: 44874
One usual approach is to do the grouping first and then join back, also if you do not want to hardcode the dates you can always use date_sub
and date_add
function to get -/+ 7 days
from the current date.
select
o.id,
e.mexpire as expire,
o.name
from objects o
join(
select object_id,max(expire) as mexpire
from objects_expire
group by object_id
having mexpire > date_sub(curdate(),interval 7 day) and mexpire < date_add(curdate(),interval 7 day)
)e
on o.id = e.object_id
Upvotes: 2
Reputation: 2588
You need to group, and to use HAVING as a filter for the grouped column
select object_id, max(expire) as expire, name
from objects_expire
left join objects on objects_expire.object_id=objects.id
group by object_id, name
having max(expire) < '2015-09-17'
and max(expire) > '2015-09-03'
Upvotes: 1