table linking problem

On 5 Oct 2002 at 0:35, XBXtXX wrote:

content
content_id | material_id | percentage
-----------------------------------------------------
1 1 100%
2 2 63.5%
2 3 31.5%
2 4 5%

material
material_id | name
------------------------------------------
1 COTTON
2 POLYESTER
3 NYLON
4 SPANDEX

The Question is,
How can I get like this :
100% COTTON
63.5% POLYESTER 31.5% NYLON 5% SPANDEX

select a.percentage,b.name from content a, material b where
a.material_id=b.material_id;

HTH

Bye
Shridhar

--
kern, v.: 1. To pack type together as tightly as the kernels on an ear of corn.
2. In parts of Brooklyn and Queens, N.Y., a small, metal object used as part
of the monetary system.

On Friday 04 Oct 2002 5:35 pm, ¼B¤tºÆ wrote:

Hi all,
I have two table, follow :

content
content_id | material_id | percentage
-----------------------------------------------------
1 1 100%
2 2 63.5%
2 3 31.5%
2 4 5%

material
material_id | name
------------------------------------------
1 COTTON
2 POLYESTER
3 NYLON
4 SPANDEX

The Question is,
How can I get like this :
100% COTTON
63.5% POLYESTER 31.5% NYLON 5% SPANDEX

Can I return the string like this ?

Not using SQL - you could define a function: material_names(int4) returning
text - provide it the "content_id" and it does the lookup and builds the
string required. You could do this with plpgsql, it's a simple language but
make sure you read the manual first. You could also use tcl/perl/c - see the
procedural languages chapter for details.

The other option is to do this in the client application.

- Richard Huxton

The Question is,
How can I get like this :
100% COTTON
63.5% POLYESTER 31.5% NYLON 5% SPANDEX

Can I return the string like this ?

Yes, it can be done with plpgsql.

You need the group operation and create an aggregate like this:

create function str_append( text, text ) returns text as '
begin
if $1 isnull then
return $2;
else
return $1 || '' '' || $2;
end if;
end;' language 'plpgsql';

create aggregate str_concat (
basetype = text,
sfunc = str_append,
stype = text
);

select str_concat( c.percentage || ' ' || m.name )
from content c, material m
where c.material_id = m.material_id
group by c.content_id;

With kind regards / mit freundlichem Gruß
Holger Klawitter
--
Holger Klawitter http://www.klawitter.de
lists@klawitter.de