Re: Complicated SQL question

Never mind.
It seems I can create a view which simplifies the whole process... once I discovered that the rest came together:

create view MESSAGE_RECIPIENTS as
select b.message_no, count(b.message_no) as recips, a.subject,

          to_char(a.message_date, 'YYYY/MM/DD HH24:MM:SS') as timestamp from message_header a, message_direction b where direction <> 1
and a.message_no = b.message_no
group by b.message_no, a.subject,

              to_char(a.message_date, 'YYYY/MM/DD HH24:MM:SS');

creates this view:

MESSAGE_RECIPIENTS

 MESSAGE_NO             NOT NULL NUMBER(8)
 RECIPS                                        NUMBER
 SUBJECT                                     VARCHAR2(120)
 TIMESTAMP                                 VARCHAR2(75)

and then this:

select a.message_no, c.address as sender, a.recips as recipients,

       a.subject, a.timestamp
from message_recipients a, message_direction b,

     email_id_x_address c
where a.message_no = b.message_no
and b.email_id = c.email_id
and b.direction = 1
order by recipients;

Yields me my output. Received on Tue Oct 18 2005 - 13:36:42 CDT