[ome-devel] how to select annotations based on two annotation links

[Bernhard Holländer]

Hi Josh!

As discussed during the OME meeting we would like to store analysis
results per plate well. This is straightforward using Annotions and
WellAnnotationLinks. Since there could be multiple measurements of a
single well, I will link the Annotation to the corresponding
ScreenAcquistion. Storing  works quite well, however, I could not
figure out how to join the two link tables in a single query to get
the results back :-(

In regular SQL I would do something like this:

SELECT a.id FROM annotation a
INNER JOIN screenacquisitionannotationlink saal ON saal.child = a.id
INNER JOIN screenacquisition sa ON sa.id = saal.parent
INNER JOIN wellannotationlink wal ON wal.child = a.id
INNER JOIN well w ON w.id = wal.parent
WHERE w.id = <well_id> AND sa.id = <acquisition_id>

... in HSQL, at least in the  form the API allows it, the join
statement does not allow to specify tables and ON clauses. So I'm a
little bit stuck how to translate this query to objects and paths.

Can you help me to build a single query to retrieve the well result
given the well id and the acquistion id?

Thanks a lot! Bernhard