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
