At 11:58 PM 7/04/2006, you 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,

Yes

>or first/skip is really only valid on the main SELECT statement?

Yes. It operates on the output.

What you will need to have a foreign key in Attachments that ties the
blob to the record in Testresults. If you want the Attachments to
come out in some fixed order, then you will need to have a sequencing
field on the attachments record, as well.

Then, you can do something like this to target a specific Attachment
in the set of attachments for a particular test:

select
tr.*,
a.attachment
from TestResults tr
join Attachments a
on a.FkID = tr.PkID
where tr.Something = 'something'
and a.SequenceNo = ?

You *could* do a SELECT FIRST on this joined set, as long as you had
something valid to order by, i.e. the SequenceNo, but the
parameterised query is much, much more efficient.

./hb