Re: query question

[Peter Brawley]

>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
>
>
>
>