SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Picking Multiple Records on Foreign Key

    I am trying to make a query to do something, but am not sure how to go about it, i.e should be aiming to JOIN tables, if so how (I hate JOINing tables, never knowing what way round to use things (which if anybody has a link to anything explaining this that'd be great for me to read to try and have a go myself!)), or should it be another way, or should I re-design my database.

    Basically, I'm building an application in a school environment, so each school has a 'year' and a 'year' can have multiple 'terms'. Currently my database is designed thus:
    YEARS(yearID, yearName)
    TERMS(termID, yearID, termName, termStart, termEnd, termHalfTerm)

    What I want to do is do a query which can: SELECT every year where either a) no terms belong to it, or b) the last term (ordered by term end, and each term cannot overlap) term end date is after today (in other words, we are somewhere in that year).
    I then wish to get the termStart date of the first term in the year, ordered by term starting date, so that the user has: {Year Name} ({Term Start} - {Term End}) as an eventual output. An years which are out of date we can safely ignore.

    So, should I remake the years table with a yearStart and yearEnd which I populate based on termStart and termEnd in it's year and update them when this changed (although this seemed clumsy, keeps data around that might grow old and just seemed like some bad database design if I could do it another way). Many thanks for any help.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adamcoppard View Post
    I am trying to make a query to do something, but am not sure how to go about it, i.e should be aiming to JOIN tables, if so how (I hate JOINing tables, never knowing what way round to use things (which if anybody has a link to anything explaining this that'd be great for me to read to try and have a go myself!)), or should it be another way, or should I re-design my database.
    This Sitepoint article may help you understand joins.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorted - had to use MIN() and MAX() in the SELECT, and then join, then sort.


Bookmarks

Posting Permissions

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