Re: Is this possible? concatenating results from a subquery

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
>