I knew I’ve seen this error before ☺ Thanks a lot.
-andrey ________________________________________ From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question >Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql> select service_names.name as 'Service', -> group_concat (hosts.name) -> from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names -> where -> hosts.host_id=services.host_id -> and service_names.servicename_id=services.servicename_id -> group by service_name.name -> -> -> ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -----Original Message----- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
