Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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) );
share|improve this answer
    
Thanks, that works perfectly. Regards –  Paul Mar 18 '13 at 5:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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