Re: query question

there should be no space between function name and () i.e. it should be

group_concat(hosts.name)

(unless you have the sql mode IGNORE_SPACE set)

Andrey Dmitriev wrote:
> I knew I’ve seen this error before ☺
>
> Thanks a lot.
>
> -andrey
>
> ________________________________________
> From: Peter Brawley [mailto:peter.brawley@earthlink.net]
> 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:baron@xaprb.com]
> 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/mysql?unsub=lists@pantek.com