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