Sql Query help: Remove Sub Selects

Hi Gang,

I'm trying to optimize a query:

This query below returns something like this:
event_id | eu_tid |      event_name       |   event_when   | day | mon | 
start   |   end    |            event_users             |       
contact_phone        | contact_pager | num_opps

----------+--------+-----------------------+----------------+-----+-----+----------+----------+------------------------------------+-----------------------------+---------------+----------
196651 |      1 | Show Event Type Color | Jul 06th, 2005 | 06  | 07  |
 
04:27 pm | 05:27 pm | {"Mickey Mouse","Donal Duck"}      |
{555-555-5555,555-555-5555} | {}            |        0  203651 |      1 | Schedule Appt.        | Jul 08th, 2005 | 08
|07  |
 
02:35 pm | 03:35 pm | {"George Bush","Bill Clinton"}     | {}                         
| {}            |        0

Instead of doing multiple selects using array_accum, I would like to use an
INNER JOIN

I'm at a stand-still. I just can't seem to get anything rolling.

Any help is greatly appreciated.

-- Start Query

SELECT eu.event_id,      eu.eu_tid,      e.event_name,      to_char(e.event_when, 'Mon DDth, YYYY') AS event_when,
to_char(e.event_when,'DD') AS day,      to_char(e.event_when, 'MM') AS mon,      to_char(e.event_when, 'HH:MI am') AS
start,     to_char((e.event_when + e.duration), 'HH:MI am') AS end,
 
      (SELECT array_accum(get_username(eu2.user_id)) FROM event_users eu2           inner join user_table ut9 ON
ut9.user_id= eu2.user_id           WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
 
event_users,
       (SELECT array_accum(ut.phone_nbr) FROM event_users eu2 INNER JOIN
user_table ut ON ut.user_id = eu2.user_id           WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
contact_phone,
       (SELECT array_accum(ut.pager_ph_nbr) FROM event_users eu2 INNER JOIN
user_table ut ON ut.user_id = eu2.user_id           WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
contact_pager,
      (SELECT count(*) FROM opp_events AS oe WHERE oe.event_id =
e.event_id) AS num_opps FROM events AS e      INNER JOIN event_users AS eu              ON eu.event_id = e.event_id
           AND eu.user_id = 4223651       LEFT JOIN event_repeats er              ON er.event_id = e.event_idWHERE
e.event_whenBETWEEN '2005-07-03 0:00:00' AND '2005-07-09 23:59:59'      AND e.status != 0 AND er.repeat_type IS
NULLORDERBY e.event_when ASC