CODEWITHSUNDEEP
mysql
Sigal Zahavi
Sigal Zahavi

Reputation: 1063

How to get min and max date smaller equal to today mysql

I need help with mysql statement. I have 2 tables, one holds events details the other the event dates.

I need to display all events dates that end today or later.

I have an event that have these dates: 29/03/2016 30/03/2016 31/03/2016

I tried this query:

SELECT a.event_id, event_name, event_location, event_desc,
MIN(a.event_date) AS from_date, MAX(a.event_date) AS to_date
FROM event_time a
LEFT JOIN event_time b
ON b.event_id = a.event_id
JOIN events c
ON a.event_id = c.event_id
WHERE b.event_date <= NOW()
GROUP BY a.event_id

But in the from_date field I get only 31/03/2016.

Can anyone please help on building the correct statement for what I need?

Thank you

Upvotes: 1

Views: 174

Answers (1)

sagi
sagi

Reputation: 40481

Well you question lack's a bit information, so if I understood you correctly, you don't need to join twice to event_times:

SELECT a.event_id, event_name, event_location, event_desc,
       MIN(a.event_date) AS from_date, MAX(a.event_date) AS to_date
FROM event_time a
JOIN events c
 ON a.event_id = c.event_id
WHERE a.event_date >= NOW()
GROUP BY a.event_id

I've changed your where clause to 

a.event_date >= NOW()

Since you said you wan't dates bigger or equal to today's date, but now I'm looking in your topic and I see you wrote the opposite, so if you wan't smaller, turn it around.

Also, in general, you are LEFT JOINING to event_time b, and you have in your where clause:

WHERE b.event_date <= NOW()

This automatically turns the join into an inner join, filters on the right table should be only in the ON clause. In your case:

LEFT JOIN event_time b
 ON b.event_id = a.event_id AND
    b.event_date <= NOW()

Upvotes: 2

Related Questions