arcgis-10.2arcgis-desktopselect-by-attributesql
I have a table with several fields. field A contains the same kind of values as does field B. I tried to select all points where the value in field A does not exist in anywhere field B using query builder in select by attribute.
For example
ID A B
1 a b
2 b c
3 c a
4 d b
It should select the points with ID 4 since value d does not exist in field B.
I think I should use a subquery, but how?
If your underlying database supports sub queries, you can build a query with a TOP N that returns ObjectIds (or any other field) and use that in your definition query.
OBJECTID IN (*query here*)
For example, here's a SQL Server query for a TOP 20, random selection:
The above query will return 20 different rows each time the map or attribute table is refreshed. This also could come in handy for an ArcMap practical joke for less experienced users (April Fools is less than a month away).
Of course, the sub query doesn't have to be a TOP N query nor do you need to use a unique id. Maybe work up a query to find duplicates:
FIRE_ID IN (SELECT FIRE_ID
FROM [SandboxGeodb].[dbo].[TESTFEATURECLASS]
GROUP BY FIRE_ID
HAVING COUNT(FIRE_ID) > 1)
As commented by @klewis ...
From the SQL reference for query expressions used in ArcGIS:
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.
Best Answer
If you use "IN" you can use a sub query that produces a list of all B values.Try: