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_id
WHERE e.event_when BETWEEN '2005-07-03 0:00:00' AND '2005-07-09 23:59:59'
AND e.status != 0 AND er.repeat_type IS NULL
ORDER BY e.event_when ASC