How would you write this simple query?

[jpschwartz]

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

[Raining_Curses]

$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..

$showID = $_GET['showID']; 

Thats if you where passing it in a link.

Hope that helps.

Raining

[Raining_Curses]

Sorry I just read your question again, and I dont think what I put is what you were looking for

[r937]

I want to write a query that lists all show names in the order of the first performance date

select showName
     , min(performanceDate) as first_performance
  from shows
inner
  join performances
    on performances.showID = shows.showID
group
    by showName
order
    by first_performance

[jpschwartz]

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

[r937]

thanks