List:General Discussion« Previous MessageNext Message »
From:Adrian Bruce Date:October 31 2007 2:37pm
Subject:Re: query question
View as plain text  
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@stripped] 
> Sent: Tuesday, October 30, 2007 1:55 AM
> To: Andrey Dmitriev
> Cc: mysql@stripped
> 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@stripped] 
> Sent: Monday, October 29, 2007 4:00 PM
> To: Andrey Dmitriev
> Cc: mysql@stripped
> 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
>
>
>
>   
>
>
>   

Thread
query questionAndrey Dmitriev29 Oct
  • Re: query questionBaron Schwartz29 Oct
    • RE: query questionAndrey Dmitriev30 Oct
      • Re: query questionPeter Brawley30 Oct
        • RE: query questionAndrey Dmitriev30 Oct
          • Re: query questionAdrian Bruce31 Oct