Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: Calendar Events Table, Very Stuck!

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •