Re: Is this possible? concatenating results from a subquery

Thanks Janko!

I was hoping for a query-only solution (SQL only), but this will work just 
great. :-)

If I switch database this code will give me some trouble.
I guess I'll have to stick to Postgresql. ;-) 

Thanks.

Regards,
Erwin Moller



Janko Richter wrote:

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