From: Christoph Haller <ch@rodos.fzk.de>
To: andresjavier.garcia@wanadoo.es
Cc: pgsql-sql@postgresql.org
Subject: Re: Query about table and catalog
Date: Thu, 05 Dec 2002 10:44:42 +0100
Message-ID: <3DEF200A.BB8F34E6@rodos.fzk.de> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, andresjavier.garcia@wanadoo.es
Subject: Re: Query about table and catalog
In-Reply-To: <3DEF200A.BB8F34E6@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox