Query about table and catalog

From: javier garcia <andresjavier(dot)garcia(at)wanadoo(dot)es>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Query about table and catalog
Date: 2002-12-04 18:08:28
Message-ID: 200212041803.gB4I35v18298@natura.cebas.csic.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Thanks for your help and have a nice day.

Javier
.............

Browse pgsql-sql by date

  From Date Subject
Next Message Pedro Igor 2002-12-04 19:19:16 Inheritance in PostgreSQL limitation
Previous Message Vernon Wu 2002-12-04 17:11:55 Re: How does postgres handle non literal string values