RE: query question

From: Andrey Dmitriev <admitriev(at)mentora.com>
Date: Tue Oct 30 2007 - 16:38:28 EDT

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=lists@pantek.com

Received on Tue Oct 30 16:39:28 2007