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.