[SOLVED] How to get the highest date from a set of records?

[r00ttap]

I have two tables. One that holds these messages along with some other information and another table that holds the dates that belong to these messages. I'm trying to only pull the messages that are >= NOW() meaning anything older than today will not display. The first "version" of this app was only able to store one date, an end date. I originally pulled the messages with this syntax:

 

SELECT * FROM messages WHERE campus = 'campus' AND CONCAT(date,' ',end_time) >= NOW() ORDER by date, start_time

 

The table looked a little liked this (along with a date field that housed the date to displayed):

 

+----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+
| id | start_time | end_time | course_name                   | campus                  | message                                   |
+----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+
| 11 | 10:00:00   | 12:00:00 | Some course name              | Campus                  | Test message with additional information. |
+----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+

 

Well now I need MULTIPLE dates for these messages and had to create the second table that houses only the ID and the dates.

 

+----+------------+
| id | date       |
+----+------------+
| 11 | 2009-06-15 |
| 11 | 2009-06-16 |
| 11 | 2009-06-17 |
| 11 | 2009-06-18 |
| 11 | 2009-06-19 |
+----+------------+

 

I've been working with this new syntax to pull the messages that looks like this:

 

SELECT * FROM messages, messages_dates  WHERE 
        campus = 'campus' AND messages.id = messages_dates.id AND 
        CONCAT(date,' ',end_time) >= NOW() GROUP BY messages.id ORDER by date, start_time

 

And it will only pull 1 record, not all of the dates listed. I know my problem is "date". What I'm asking here is how do I get the last or "MAX(date)" from the dates table so that I can only select messages that are greater than or equal today, anything older than today will not be shown.

[kickstart]

Hi

 

Probably your group by clause that is confusing things. Initial idea, change it to GROUP BY messages_dates.id

 

All the best

 

Keith

[r00ttap]

kickstart,

 

This works, as well as my original statement but the problem is that it only pulls 1 record. I want it to pull everything that >= NOW().

 

So if the dates were:

2009-06-14

2009-06-15

2009-06-16

2009-06-17

2009-06-18

2009-06-19

 

It will show the message until the 19th, after the 19th it won't pull the messages anymore.

[kickstart]

Hi

 

More of a look, and while I think I am on the right track, my above idea is garbage.

 

Not sure you need the GROUP BY at all.

 

All the best

 

Keith

[r00ttap]

Actually the GROUP BY is exactly what a wanted. I must have spelled something wrong because it's working perfectly now. Thanks for you time and help kickstart!