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


Wed, 21 Apr 2004 15:34:20 +0200
Erwin Moller
	<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
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
> 

Wed, 21 Apr 2004 16:06:26 +0200
Janko Richter <jankorichter@yahoo.de>
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
>>


Wed, 21 Apr 2004 17:52:19 +0200
Erwin Moller
	<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
Re: Is this possible? concatenating results from a subquery