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?

We are running ver5.

Thanks,
Andrey

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

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

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

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

there should be no space between function name and () i.e. it should be

group_concat(hosts.name)

(unless you have the sql mode IGNORE_SPACE set)



Andrey Dmitriev wrote:
> 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
>
>
>
>
>
>
>