0

I have the following query:

SELECT master.master_id, master.showname, 
MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date, 
MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS last_show_date, master.showinfo
       FROM master LEFT OUTER JOIN
       shows ON master.master_id = shows.master_id 
LEFT OUTER JOIN 
       realtimeticketing rtt ON master.realtimeticketing_id = rtt.realtimeticketing_id 
LEFT OUTER JOIN 
       event_types ON master.event_type = event_types.event_types_id
       WHERE   (master.domainlist LIKE '%INTERNET%') 
       GROUP BY shows.master_id
       ORDER BY first_show_date, master.showname LIMIT 100;

This query returns the following output:

enter image description here

The requirement is that master_id 281 should be displayed as the first record since show dates lie within current date. And after 6th April 2014, the id 282 should start showing up.

I have tried various WHERE clauses but if I use WHERE shows.show_date >= Now(), it starts showing show dates from current date. I however require the first_show_date i.e. 01-03-2014 for master_id 281

1

2 Answers 2

1

Looks like you want your current date to be before or on last show date. If that is the case try following HAVING clause.

HAVING MAX(CAST(CONCAT(shows.show_date, ' ', shows.show_time) AS DATETIME) >= Now()
2
  • 1
    Should be a HAVING clause, not WHERE Apr 2, 2014 at 15:21
  • Agreed. This is a GROUP BY query and the condition is for an aggregate field. Thanks for correcting. Apr 2, 2014 at 15:23
0

No experience from MySQL, but in other systems the next idea works

WHERE shows.show_date >=DATEADD(NOW(),1)

it should compare show_date to now + 1 day (see Mysql page) If you want just compare dates use ( if you are interested shows tomorrow not shows 24 hours later)

WHERE DATE(shows.show_date) >= DATEADD(DATE(NOW()),1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.