Creating Spatial View ... GIS/SQL

Question

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

Answer 1

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.

• Diego Gonzalez: Wow, this definitely did the trick! I appreciate the help !!!