Is this possible? concatenating results from a subquery

Hi!

I face the following problem:
2 tables: tblperson and tblnotes
tblperson:
colums: personid (PK), name

tblnotes:
colums: noteid(PK), personid(references tblperson(personid)), note

tblnotes has notes stored written by a person from tblperson identified (FK)
by its personid.

I make a select on one table with certain criteria and want to have a
concatenation on a subquery results.
Something like this:

SELECT
P.personid,
P.name,
concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) )
AS allnotesbythisperson
FROM tblperson AS P WHERE (P.personid=34);

The concat word I use is pure fantasy.
Is this at all possible?

I know I can easily circumvent te problem by my scriptinglanguage (PHP), but
that will result in many extra queries.

How do I proceed?

TIA!!

Regards,
Erwin Moller

Perhaps this helps:

CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat, -- is function of operator 'text || text'
STYPE = text,
INITCOND = ''
);

SELECT
P.personid,
P.name,
concat( N.note ) AS allnotesbythisperson
FROM tblperson AS P
INNER JOIN tblnotes AS N ON N.personid=P.personid
WHERE P.personid=34
GROUP BY P.personid, P.name;

Regards, Janko

Erwin Moller wrote:

Hi!

I face the following problem:
2 tables: tblperson and tblnotes
tblperson:
colums: personid (PK), name

tblnotes:
colums: noteid(PK), personid(references tblperson(personid)), note

tblnotes has notes stored written by a person from tblperson identified
(FK) by its personid.

I make a select on one table with certain criteria and want to have a
concatenation on a subquery results.
Something like this:

SELECT
P.personid,
P.name,
concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) )
AS allnotesbythisperson
FROM tblperson AS P WHERE (P.personid=34);

The concat word I use is pure fantasy.
Is this at all possible?

I know I can easily circumvent te problem by my scriptinglanguage (PHP),
but that will result in many extra queries.

How do I proceed?

TIA!!

Regards,
Erwin Moller