[GIS] ST_Intersects with subquery in Spatialite

Question

I am having issues with what seems to be a very simple spatial query with a sub-query in Spatialite. I am trying to find all the buildings (poly) that intersect with a service area (poly), but only buildings that have a specific subtype code and with stories greater than a value (see query below). I am assuming it is a syntax error, but cannot seem to figure it.

SELECT b.pkid, b.subtype, b.stories
FROM bldg AS b, service_area AS a
  (SELECT b.subtype, b.stories
   FROM bldg
   WHERE subtype IN ('x', 'y', 'z') AND stories > 16)
WHERE ST_Intersects(b.geometry, a.geometry)

ERROR

Query execution error on CREATE TEMP VIEW _tview AS SELECT b.pkid, b.subtype, b.stories
FROM bldg AS b, service_area AS a
  (SELECT b.subtype, b.stories
   FROM bldg
   WHERE subtype IN ('x', 'y', 'z') AND stories > 16)
WHERE ST_Intersects(b.geometry, a.geometry): 1 - near "(": syntax error

EDIT

Query to select desired subtype and buildings > 16 stories

ST_Intersects query – buildings and service area (buildings shorter than 16)

I did get the following query to work, but its not a subquery and not the most optimal:

 SELECT b.pkid, b.subtype, b.stories
 FROM bldg AS b, service_area AS a
 WHERE ST_Intersects(b.geometry, a.geometry) AND b.subtype IN ('x', 'y', 'z') AND b.stories > 16

Answer 1

You are missing a "," in your FROM before "("