> 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
AScod_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
