oracle8.1.6 select notifications.notification_id from notifications minus select nnr.notification_id from (select notification_id, user_id from notifications, notification_requests where notifications.type_id = notification_requests.type_id and notifications.object_id = notification_requests.object_id) nnr, notification_user_map where nnr.notification_id = notification_user_map.notification_id(+) and nnr.user_id = notification_user_map.user_id(+) and notification_user_map.sent_date is null select nnr.* from (select notifications.notification_id, notifications.notif_subject, notifications.notif_text, notifications.notif_html, notification_requests.user_id, notification_requests.type_id, notification_requests.delivery_method_id, notification_requests.request_id, notifications.response_id, notifications.notif_date, notifications.notif_user from notifications, notification_requests where notifications.type_id = notification_requests.type_id and notifications.object_id = notification_requests.object_id and notification_requests.interval_id = :interval_id) nnr, notification_user_map, acs_objects where nnr.notification_id = notification_user_map.notification_id(+) and nnr.user_id = notification_user_map.user_id(+) and notification_user_map.sent_date is null and acs_objects.object_id = nnr.request_id and acs_objects.creation_date <= nnr.notif_date order by nnr.user_id, nnr.type_id, nnr.notif_date