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 >> ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
