2

I have created an enterprise geodatabase in my local instance of SQL Server and I want to create one select statement of multiple select statements within multiple tables and feed this into a view.

I currently have a point feature class (point1) with (name, address, and feature_id, SHAPE).
An example of name (attribute) is Jim.

Related table (table1) that is related to the point feature class.
(feature_id, category, open) I have managed to create a select statement showing the data I need between both of these. Which is as below:

SELECT POINT1.FEATURE_ID, NAME, TABLE1.CATEGORY, TABLE1.OPEN
FROM POINT1
INNER JOIN TABLE1 ON POINT1.FEATURE_ID=TABLE1.FEATURE_ID


   FEATURE_ID | NAME     | CATEGORY |  OPEN
    ---------------------------------------------
    1         | JIM      | FUNNY    | NO
    2         | DWIGHT   | KNIGHT   | YES
    3         | MICHAEL  | BOSS     | YES

I have two other polygon feature classes.

1.) CITIES: (NAME, NUMBER, SHAPE)
2.) ZIP: (ZIP, SHAPE)

I have also managed to create a select statement as follows. Points that intersect with the polygon.

SELECT P1.NAME, C.NUMBER Number    
FROM CITIES C, NAME P1   
WHERE C.Shape.STIntersects (P1.SHAPE) = 1    

NAME     |  Number
----------------------
JIM      | 212
DWIGHT   | 200
MICHAEL  | 266

The other statement is as follows:

SELECT P1.NAME, Z.ZIP Zip  
FROM ZIP Z, NAME P1  
WHERE Z.Shape.STIntersects (P1.SHAPE) = 1   

NAME     |  ZIP
----------------------
JIM      | 94203
DWIGHT   | 80201
MICHAEL  | 72201

I'm trying to merge these SELECT statements together to feed them in a view to display points with different fields from the related table, cities, and zip.

I am looking for something that looks similar to this ...

    FEATURE_ID | NAME     | CATEGORY |  OPEN |  Number  | ZIP
    -----------------------------------------------------------------
    1          | JIM      | FUNNY    | NO    |  212     | 94203
    2          | DWIGHT   | KNIGHT   | YES   |  200     | 80201
    3          | MICHAEL  | BOSS     | YES   |  266     | 72201
0

You can combine all those into one query using INNER JOIN. Try and see if this query works:

SELECT P1.FEATURE_ID, P1.NAME, TABLE1.CATEGORY, TABLE1.OPEN, C.Number, Z.Zip
FROM POINT1 P1
INNER JOIN TABLE1 ON P1.FEATURE_ID=TABLE1.FEATURE_ID
INNER JOIN CITIES C ON C.Shape.STIntersects(P1.SHAPE) = 1
INNER JOIN ZIP Z ON Z.Shape.STIntersect(P1.SHAPE) = 1

If that returns results that looks reasonable, you can make a View from that using the steps described in the ArcGIS help page.

  • Wow, this definitely did the trick! I appreciate the help !!! – Diego Gonzalez May 14 at 14:22

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.