Re: How to form an SQL Query

From: Larry Coon <lcnospam_at_assist.org>
Date: Tue, 03 Aug 2004 10:29:21 -0700
Message-ID: <410FCB71.15E7@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 Received on Tue Aug 03 2004 - 12:29:21 CDT