Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2993D475BC3
	for <pgsql-sql@postgresql.org>; Thu,  5 Dec 2002 04:44:45 -0500 (EST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id 02874475921
	for <pgsql-sql@postgresql.org>; Thu,  5 Dec 2002 04:44:44 -0500 (EST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu Dec 05 10:44:43 2002 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id KAA06318; Thu, 5 Dec 2002 10:44:42 +0100 (MET)
Message-ID: <3DEF200A.BB8F34E6@rodos.fzk.de>
Date: Thu, 05 Dec 2002 10:44:42 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.06 [en] (X11; I; HP-UX B.10.20 9000/780)
MIME-Version: 1.0
To: andresjavier.garcia@wanadoo.es
Cc: pgsql-sql@postgresql.org
Subject: Re: Query about table and catalog
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/65
X-Sequence-Number: 10653

> I've got a table with several fields. Among others there are the
fields
> 'soil1', 'soil2', and 'soil3' that are char type. A row can have an
empty
> value in one of these fields, or the three fields can have valid
values:
>
>  cod_grass |    suelo1                        |    suelo2       |
suelo3
> -------+--------------+--------+-----------------------------
>       2590 | Xerosoles petrocalcicos    |                      |
>        181 | Xerosoles calcicos           |
|
>        265 | Xerosoles petrocalcicos  |                         |
>        593 | Zona urbana                   |
|
>       1112 | Cambisoles calcicos       |                     |
>          2 | Litosoles
|                       |
>          3 | Xerosoles calcicos            |                    |
>          4 | Litosoles                           | Rendsinas
aridicas     |
>          5 | Xerosoles petrocalcicos   |                       |
>          6 | Litosoles
|                      |
>          7 | Regosoles calcaricos       | Xerosoles calcicos
> ...
>
> In other table I've got a catalog of posible soil types, assigning an
integer
> value to each of possible soil types.
>
>               tipo_suelo              | cod_tipo_suelo
> -------------------------------------+----------------
>  Arenosoles albicos                  |              1
>  Cambisoles calcicos                 |              2
>  Cambisoles eutricos                 |              3
>
> Is it possible to prepare a query that show the contents of the table
of
> soils and aditional columns after each of the soils fields, showing
the
> corresponding numerical code for that soil, extracted from the
catalog?
>
> I just know how to do this for one of the soils:
>
> SELECT cod_grass, suelo1,cod_tipo_suelo AS cod_suelo1 FROM
> suelos,suelos_catalogo WHERE suelo1=tipo_suelo ORDER BY cod_grass;
>
> But I would like to do the same for the three at a time.
>
Try this (untested) or something similar:
SELECT cod_grass,
                  suelo1, st1.cod_tipo_suelo AS cod_suelo1
                  suelo2, st2.cod_tipo_suelo AS cod_suelo2
                  suelo3, st3.cod_tipo_suelo AS cod_suelo3
FROM    suelos, suelos_catalogo st1,  suelos_catalogo st2,
suelos_catalogo st3
WHERE suelo1=st1.tipo_suelo
  AND suelo2=st2.tipo_suelo
  AND suelo3=st3.tipo_suelo
ORDER BY cod_grass;

I'm curious why you did not design the tables vice versa.
Table "suelos" just holding "cod_tipo_suelo",
so queries like the above would run much faster, because
only integers have to be compared instead of strings.

Regards, Christoph