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; |
|