| Code: |
SELECT d.name, x.cnt, x.notifiers
FROM device d LEFT OUTER JOIN
(SELECT server_id,
map_id,
device_id,
COUNT(o.*) AS cnt,
ARRAY_TO_STRING(
ARRAY (
SELECT n.name
FROM notifier n,
notifierrule i
WHERE i.server_id = o.server_id
AND i.map_id = o.map_id
AND i.device_id = o.device_id
AND n.server_id = i.server_id
AND n.notifier_id = i.notifier_id
ORDER BY n.name
), ', ') AS notifiers
FROM notifierrule o
GROUP BY o.server_id, o.map_id, o.device_id
) x ON d.server_id = x.server_id
AND d.map_id = x.map_id
AND d.device_id = x.device_id
WHERE d.delete_time = 'infinity'
ORDER BY d.name; |
|
