CODEWITHSUNDEEP
mysql
MarkusHH
MarkusHH

Reputation: 153

Select max value from joined table

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

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

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

AdrianBR
AdrianBR

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

Related Questions