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