how to detect polygons within other (many) polygons in postgis

Question

I have two datasets: 1. ZipCodes and 2. Neighborhoods (think of them as like counties).

I want to join each neighborhood with which zipcodes cover it. Most neighborhoods will only be within one zipcode, but in some cases neighborhoods will straddle two. So for example:

Neighborhood 1 is inside 20001
Neighborhood 2 is inside 20002
Neighborhood 3 is inside 20001,20002

Here is what I have so far:

SELECT name, zipcode  
FROM    
neighborhood_names nn, dc_zipcode_boundries dzb
WHERE ST_Intersects(nn.the_geom, dzb.the_geom);

Note: Updated to within based on comments, now getting an answer for each neighborhood but still not able to get the Array function to respond as expected.

Answer 1

I figured it out. thanks to the help from John. My statement needed a group by (whcih is what the error said, just needed some time to digest before it clicked).

the snippet below worked for anyone following

SELECT name, array_to_string(array_agg(zipcode), ',') 
FROM    
neighborhood_names nn, dc_zipcode_boundries dzb
WHERE ST_Intersects(nn.the_geom, dzb.the_geom)
group by name