Re: How to form an SQL Query

"Larry Coon" <lcnospam_at_assist.org> wrote in message news:410FCB71.15E7_at_assist.org...
> Chris Walsh wrote:
>
> > Hi,
> > I've got two tables (master and detail) with a many-to-one
> > relationship between them. I want to to perform a query which will
> > join them but ONLY the first detail record for each master i.e.
> >
> > Pseudo definitions are:
> >
> > TABLE master =
> > m_id int,
> > m_text varchar
> >
> > TABLE detail =
> > d_id int,
> > d_masterID int, (foreign key to master.m_id)
> > d_date smalldatetime
> >
> > I basically want to extract the following fields:
> > m_id, m_text, d_date
> >
> > where d_date is the oldest detail record for a given m_id and I don't
> > want to get multiple master rows back.
>
> Something along the lines of:
>
> select m.m_id, m.m_text, min(d.d_date)
> from master m
> inner join detail d
> on d.d_masterID = m.m_id
> group by m.m_id, m.m_text
>
> Disclaimer: This is off the top of my head. I didn't try
> it myself. YMMV, and syntax may vary by implementation.
>
>
> Larry Coon
> University of California

Larry, don't you recognize a class assignment when you see one? :) Received on Tue Aug 03 2004 - 19:53:34 CEST