> 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.

This is one way:

SELECT FIELD1, FIELD2,
(SELECT FIRST 1 FIELD1 FROM DETAILTABLE WHERE DETAILTABLE.FK=MASTERTABLE.ID
ORDER BY SOMETHING) DETAILFIELD1 ,
(SELECT FIRST 1 FIELD2 FROM DETAILTABLE WHERE DETAILTABLE.FK=MASTERTABLE.ID
ORDER BY SOMETHING) DETAILFIELD2
FROM MASTERTABLE WHERE <mastertable row selection criteria>

The server may object to the subselect (not recognise it as a singleton) -
the only other way I know is to write selectable store procedure with
similar syntax.
Just remember that without an order by in the subselects you won't get what
YOU think is the "first" record of the detail table.
Alan