Обсуждение: table linking problem

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 ?
Thanks.

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