1

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

  • Is your goal a new table with the fields GID, SITE, SITE_desc, SPECIES and GEOM? Or do you just want to query both tables at the same time? Both are definitely possible. – L_Holcombe Mar 18 '13 at 4:06
  • Preferably looking to create a new table, the issue i'm having is (since there is multiple occurences of the same site code in the records table and only one row per site code in the second table) getting it to put the correct value for SITE_desc against the repeated site codes. – Paul Mar 18 '13 at 4:25
2

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) );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.