pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery about table and catalog
2+ messages / 2 participants
[nested] [flat]
* Query about table and catalog
@ 2002-12-04 18:08 javier garcia <andresjavier.garcia@wanadoo.es>
0 siblings, 0 replies; 2+ messages in thread
From: javier garcia @ 2002-12-04 18:08 UTC (permalink / raw)
To: 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
.............
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query about table and catalog
@ 2002-12-05 09:44 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2002-12-05 09:44 UTC (permalink / raw)
To: andresjavier.garcia@wanadoo.es; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-05 09:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-04 18:08 Query about table and catalog javier garcia <andresjavier.garcia@wanadoo.es>
2002-12-05 09:44 Re: Query about table and catalog Christoph Haller <ch@rodos.fzk.de>
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