List:General Discussion« Previous MessageNext Message »
From:Andrey Dmitriev Date:October 30 2007 3:59am
Subject:RE: query question
View as plain text  
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