How to form an SQL Query

From: Chris Walsh <chris.walsh_at_real-sense.com>
Date: 3 Aug 2004 09:06:49 -0700
Message-ID: <1691b372.0408030806.102349af@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.

Chris. Received on Tue Aug 03 2004 - 11:06:49 CDT