Tom,
If you're interested, here's the query I ended up with. It's much uglier than
the original query, but gives me slightly more data (one bit of information
is seperated into 2 columns rather than rolled up), is 100ms faster, and
should not slow down much with the growth of the tables:
SELECT users.user_id, (users.fname || COALESCE(' ' || users.minit, '') || ' '
|| users.lname) as atty_name,
users.lname,
COALESCE (
(SELECT if_addendee_conflict(users.user_id, 3272, '2003-04-15 10:00', '1
days'::INTERVAL,
events.event_id, events.event_date, events.duration, event_cats.status,
'30 minutes', staff_id) as cflt
FROM event_types, event_cats, event_days, events, event_staff
WHERE events.event_id = event_days.event_id
and events.etype_id = event_types.etype_id
AND event_types.ecat_id = event_cats.ecat_id
AND event_days.event_day BETWEEN '2003-04-15' AND '2003-04-16 10:00'
AND events.event_id <> 3272
AND events.event_id = event_staff.event_id
AND event_staff.staff_id = users.user_id
AND event_cats.status IN (1,3)
ORDER BY cflt LIMIT 1),
(SELECT 'LEAVE'::TEXT
FROM event_types, event_cats, event_days, events
WHERE events.event_id = event_days.event_id
and events.etype_id = event_types.etype_id
AND event_types.ecat_id = event_cats.ecat_id
AND event_days.event_day BETWEEN '2003-04-15' AND '2003-04-16 10:00'
AND events.event_id <> 3272
AND event_cats.status = 4)
) AS conflict,
(SELECT (staff_id > 0) FROM event_staff
WHERE event_id = 3272
AND staff_id = users.user_id) as assigned
FROM users
WHERE EXISTS (SELECT teams_users.user_id FROM teams_users JOIN teams_tree
ON teams_users.team_id = teams_tree.team_id WHERE teams_tree.treeno
BETWEEN 3 and 4 AND teams_users.user_id = users.user_id)
AND users.status > 0
ORDER BY conflict, users.lname, atty_name;
--
-Josh Berkus
Aglio Database Solutions
San Francisco
