subquery in select by attribute in Arcgis

Question

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?

Answer 1

If you use "IN" you can use a sub query that produces a list of all B values.Try:

NOT A IN (SELECT B FROM TABLE_NAME)

Answer 2

You should use the subquery:

FieldA not in (select FieldB from tablename)

When using the Select By Attributes tool.

ArcGIS Help page.

• ed.hank: I did not know of this query before, very interesting to know!
• Brent Edwards: Be careful if/when working with versioned data in ArcSDE databases. In these cases you need to ensure that the data in your subquery is a versioned view - resources.arcgis.com/EN/HELP/MAIN/10.1/index.html#//…. Otherwise, your query may return incorrect results.