5

I need to 'extract' a list of variables based on the IDs from another table. Using a subquery seems to be the best method. I have a large list of variables and have outside calculated a second list. This second list does not have coordinate data attached to it, so I need to select the same values in the original list to pull the data out. The shapefile and table are both in the catalog.

I try to do a simple select by attribute. It gives me the 

SELECT * FROM US_Maize_All WHERE: "pointid" = (SELECT "pointid" FROM Maize_Table)

but this only gives me a SQL expression error. The "pointid" parameters are selected from the option box. As far as I can tell this is the exact format as any other info I can find online. Has to be something I'm overlooking why this will not work.

Improve this question
2
  • if you use equality sign in where clause,subquery must return only one record and your query may return more than one value.Try this query I hope it works: Select * From US_Maize_All Where pointid IN (Select pointId from Maize_Table) and I think also fields name must not be in "" because they interpreted as fixed values.So I did not use "" for pointid field name.
    – Reza
    Commented Aug 11, 2014 at 19:32
  • I had tried the IN already but no dice. I took out the " ", but Arc put them in there to start with. This is just in the standard dialog box, not in Python.
    – twistr25
    Commented Aug 11, 2014 at 20:15

2 Answers 2

Reset to default
3

Nested or subqueries in ArcGIS are only available when using geodatabase formats, per the help files - bottom of this one which also links to this more detailed one about halfway down the page.

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

If you convert your shapefile to a geodatabase feature class and bring the table into the geodatabase as well, you should be able to use the desired query. I believe the syntax for the dialog would be:

"pointid" IN (SELECT "pointid" FROM Maize_Table)

Some syntax examples are given in the above linked help files. The same restriction applies to Python, but they syntax may differ.

Improve this answer
0

If you don't want to convert everything to a geodatabase, maybe you could try to just join them in arc, with pointid as the join field.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.