From: Rob <robo_spamcatcher_this_is_invalid_too@speakeasy.net>
Subject: Sql Query help: Remove Sub Selects
Date: Sun, 10 Jul 2005 19:20:57 -0400
User-Agent: KNode/0.8.0
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
Message-ID: <ELidnd27-Y_lMEzfRVn-oQ@speakeasy.net>
Lines: 61
To: pgsql-sql@postgresql.org

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