Re: SQL help?

Skip Evans wrote: > Hey all, > > I have a SQL requirement I'm not quite sure how to compose. > > I have two tables, shows, and shows_dates. It's a one to many > relationship where there is a single entry in shows and multiple entries > in shows_dates that list each date and time for a play production for a > run of entries in shows, like > > I need a query that will read each record in shows, but I only want the > first record from shows_dates, the first one sorted by date, so I can > display all shows in order of their opening date. > > Not sure how to grab just the first record from shows_dates though. > > Hint, anyone? I guess this should work in most databases, it does in MySQL: select q.name, min(q.date) as firstdate from (select s.id, s.name, d.date from shows s, shows_dates d where d.shows_id = s.id) as q group by q.id order by firstdate; A left join in the sub query will also grab the shows for which no shows_dates row exist: select q.name, min(q.date) as firstdate from (select s.id, s.name, d.date from shows s left join shows_dates d on d.shows_id = s.id) as q group by q.id order by firstdate