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_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.
Date: 3 Aug 2004 09:06:49 -0700
Message-ID: <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.
Chris. Received on Tue Aug 03 2004 - 18:06:49 CEST