[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: query question
From:       Adrian Bruce <abruce () stvincent ! ac ! uk>
Date:       2007-10-31 14:37:26
Message-ID: 47289326.4020809 () stvincent ! ac ! uk
[Download RAW message or body]

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=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]