oracle8.1.6
select notification_id
from notifications
where not exists (select notifications.notification_id
from notifications,
notification_requests,
notification_user_map
where notifications.type_id = notification_requests.type_id
and notifications.object_id = notification_requests.object_id
and notifications.notification_id = notification_user_map.notification_id(+)
and sent_date is null)
select notifications.notification_id,
notif_subject,
notif_text,
notif_html,
notification_requests.user_id,
notification_requests.type_id,
notification_requests.delivery_method_id,
notifications.response_id,
acs_object.name(notifications.object_id) as object_name
from notifications,
notification_requests,
notification_user_map
where notifications.type_id = notification_requests.type_id
and interval_id = :interval_id
and notifications.object_id = notification_requests.object_id
and notifications.notification_id = notification_user_map.notification_id(+)
and sent_date is null