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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
