RE: query question

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, 

    -> where
    ->     hosts.host_id=services.host_id
    -> and service_names.servicename_id=services.servicename_id
    -> group by service_name.name
    ->
    ->
    -> ;

-----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().

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

Baron

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com