I have two tables with a one-to-many relationship, using the primary key of the master record in each record of the detail table. I would like to create a select statement that retrieves information from the master table as well as the first record from the detail table for each record in the master table. Is there a SQL syntax for doing this? I have been playing with "join", but cannot figure out how to get just the first detail record rather than all detail records.

I want something like the following, but only get the first detail record, not all of them:

select m.id, m.field1, m.field2, m.field3, m.field4, d.field1, d.field2
from mastertable m
inner join detailtable d on d.masterid = m.id
where <mastertable row selection criteria>;


TIA,

Dan.