postgresql 7.2 select ci.item_id as photo_id, (select pp.caption from pa_photos pp where pp.pa_photo_id = ci.live_revision) as caption, i.image_id as thumb_path, i.height as thumb_height, i.width as thumb_width, latest() as seed from cr_items ci, cr_items ci2, cr_child_rels ccr2, images i where $size_clause and ci.item_id = ccr2.parent_id and ccr2.child_id = ci2.item_id and ci2.live_revision = i.image_id and ci.live_revision is not null $photo_clause order by seed limit 1 select site_node__url(sn.node_id) from site_nodes sn inner join pa_package_root_folder_map pm on (sn.object_id = pm.package_id) inner join cr_items parent on (pm.folder_id = parent.item_id) inner join cr_items child on (parent.tree_sortkey <= child.tree_sortkey) where child.tree_sortkey between parent.tree_sortkey and tree_right(parent.tree_sortkey) and tree_level(parent.tree_sortkey) = 2 and child.item_id = :photo_id select ci.item_id as photo_id, (select pp.caption from pa_photos pp where pp.pa_photo_id = ci.live_revision) as caption, i.image_id as thumb_path, i.height as thumb_height, i.width as thumb_width from cr_items ci, cr_items ci2, cr_items root, cr_child_rels ccr2, images i where $size_clause and ci.item_id = ccr2.parent_id and ccr2.child_id = ci2.item_id and ci2.live_revision = i.image_id and ci.live_revision is not null and ci.tree_sortkey between root.tree_sortkey and tree_right(root.tree_sortkey) and root.item_id = :root_folder_id order by ci.item_id desc limit 1 and ci.item_id = :photo_id ccr2.relation_tag = 'viewer' ccr2.relation_tag = 'thumb'