From: Date: October 30 2007 4:59am
Subject: RE: query question
List-Archive: http://lists.mysql.com/mysql/209778
Message-Id: <"L5619D61A750A47c18F2A32ED7936DD48.1193716763.mt-adm.mentora.biz*"@MHS>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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,=20
monarch.service_names as service_names
    -> where
    ->     hosts.host_id=3Dservices.host_id
    -> and service_names.servicename_id=3Dservices.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]=20
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=20
the
> use of analytical functions. Or in the worst case by writing a=20
function.
>=20
> But basically I have a few tables
> Services, Hosts, service_names
>=20
>=20
> And I can have a query something like=20
>=20
>=20
> select service_names.name as 'Service', hosts.name as 'Host'
> from hosts, services, service_names=20
> where=20
>     hosts.host_id=3Dservices.host_id=20
> and service_names.servicename_id=3Dservices.servicename_id=20
> order by service_names.name
>=20
> Which outputs something like
>=20
> | SSH                                                 | mt-ns4        =20
=20
> |
> | SSH                                                 | tsn-adm-core  =20
=20
> |
> | SSH                                                 | tsn-juno      =20
=20
> |
> | SSH                                                 | tsn-tsn2     =20
>=20
> However, the desired output is one line per service name, so something
> like
>=20
> | SSH                                                 | mt-ns4,
> tsn-adm-core, tsn-juno, tsn-tsn2 |
>=20
>=20
> Can this be done w/o writing procedural code in mysql?

Yes.  Have a look at GROUP_CONCAT().

Baron