Thread: Query about table and catalog
Hi;
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 |
Xerosolescalcicos
...
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 | 1Cambisoles
calcicos | 2Cambisoles 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.
Thanks for your help and have a nice day.
Javier
.............
> 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