SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast jpschwartz's Avatar
    Join Date
    Jul 2003
    Location
    Topeka
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How would you write this simple query?

    My database stores theatrical shows and the performance dates for each show in two simple tables:

    shows
    showID
    showName

    performances
    performanceID
    performanceDate
    showID

    A show may have multiple performance dates. I want to write a query that lists all show names in the order of the first performance date for each show. How would you write that query?

    -Jim

  2. #2
    SitePoint Evangelist Raining_Curses's Avatar
    Join Date
    May 2005
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql = ("SELECT * FROM performances WHERE showID = '".$showID."' ORDER by performanceDate asc"); 
    That would work I think, but you would have to put the showID value you want into the var $showID before the query.

    With something like..

    PHP Code:
    $showID $_GET['showID']; 
    Thats if you where passing it in a link.

    Hope that helps.

    Raining
    www.thetrial.staronesw.com
    Check out my online text based rpg (In development!!)

  3. #3
    SitePoint Evangelist Raining_Curses's Avatar
    Join Date
    May 2005
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I just read your question again, and I dont think what I put is what you were looking for
    www.thetrial.staronesw.com
    Check out my online text based rpg (In development!!)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jpschwartz
    I want to write a query that lists all show names in the order of the first performance date
    Code:
    select showName
         , min(performanceDate) as first_performance
      from shows
    inner
      join performances
        on performances.showID = shows.showID
    group
        by showName
    order
        by first_performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast jpschwartz's Avatar
    Join Date
    Jul 2003
    Location
    Topeka
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Amazing!

    That works great! I never would have stumbled on it. That problem and its solution should be in a tutorial.

    My great admiration for your skill and my thanks for your generosity!

    Jim

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •