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

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