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
>