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