PostGIS: Querying a record table and explanatory table

Question

Basically I have two tables one containing a collection of records and one table that contains extra info about features in the first. i.e

Records Table: GID SITE SPECIES GEOM

and the second table has GID SITE SITE_desc

Essentially the first table has many records for the same site while the second table only has one record (in one row) for each site with a description.

What i'm wondering is if there is anyway i can query both tables (for a specific species for example) and have the field Site_desc be printed in a new column for each record where the sites are the same (SITE=SITE).

Is this possible?

Regards Paul

Answer 1

You need to make a query containing a Join.

I think somethings similar to this should work:

SELECT s.SITE, s.SITE_desc, f.SPECIES, f.GEOM
FROM firsttable f
JOIN secondtable s ON (f.SITE=s.SITE);

If you need to make a new layer, just use the following:

CREATE TABLE newtable as(
SELECT s.SITE as site, s.SITE_desc as site_desc, f.SPECIES as species, f.GEOM as geom
FROM firsttable f
JOIN secondtable s ON (f.SITE=s.SITE) );