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