--- In firebird-support@yahoogroups.com, Tiago Mikhael Pastorello
Freire wrote:

>
> Hi,
>
> I a list of test results in one table, and another table which
> stores attachments in blob fields. so, in a join, each test
> result may show up one or more times, depending on the number of
> attachments it has. I would like to do something like
>
> select * from test_results
> left join first 1 skip 0 attachments
> where ...
>
> But it does not seem to work.
> How can I accomplish that? Am I borking the syntax, or first/skip
> is really only valid on the main SELECT statement?

Yep, Tiago, it is only valid on the main SELECT. There are two ways to
do what you want, one of them involves a subselect for each field you
want to return from attachments, the other one is like this:

select * from test_results tr
left join attachments a on a.test_results_id = tr.id
where not exists(select * from attachments a2
where a2.test_results_id = tr.id
and a2.id < a.id)

I.e. only return the attachment with the lowest ID for each
test_result.

Note that using LEFT join indicates that there may be test_results
without attachments. If this is not the case, then remove LEFT.

HTH,
Set