Calendar Events Table, Very Stuck!

chricholson · #1 (**permalink**) 03-02-09, 17:21

Hi,

I'm having a great deal of difficulty with a database for a website, in particular the appropriate method for calling the data I want which makes me question my original design.

I have three tables: Sections, Terms and TermEvents. Sections has just a sectionID and a sectionName. Terms has a termID, termTitle and termStartDate. TermEvents has eventsID, eventTitle, eventDescription, eventDate, termID, sectionID.

From this I get the relationship so that one term can have many events, and one section can be linked with many events. When I come to calling upon it in my application, I need some way to call the most recent term (so using termStartDate < getDate()) and then displaying all the relevant events for that term (so matching the termID from TermEvents with the same as the termID from Terms).

I am trying to use a mixture of ASP VB and ASP Repeaters but this is proving harder than I first imagined. I can call upon a specific term by defining the termID and this displays the correct events. However, I need to find some way to call upon the date of the most recent term, then somehow get the ID of this term and use that to call upon appropriate events. Hoping someone has done something similar!

Many thanks in advance,

Chris

gvee · #2 (**permalink**) 03-03-09, 03:39

Code:

SELECT Max(termstartdate) As most_recent_term
FROM   terms
WHERE  termstartdate < GetDate()

Code:

SELECT terms.id
FROM   terms
 INNER
  JOIN (
        SELECT Max(termstartdate) As most_recent_term
        FROM   terms
        WHERE  termstartdate < GetDate()
       ) As x
    ON terms.termstartdate = x.most_recent_term

Internet Services

Web Development

Digital Marketing

Consumer Tech