Re: How to form an SQL Query

"Chris Walsh" <chris.walsh_at_real-sense.com> wrote in message news:1691b372.0408030806.102349af_at_posting.google.com...
> 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.
>
> I've tried an INNER JOIN but clearly get ALL detail records joined
> with duplicates of master.
>
> I've tried using DISTINCT but as multiple d_dates exist for a given
> m_id, all rows are retrieved.
>
> I've tried TOP 1 but this only returns one row not one detail row per
> master.
>
> Any ideas? Thanks.

Hint: corelated qubquery Received on Tue Aug 03 2004 - 18:30:05 CEST