Basic Select Statements

All select statements must contain the fields that will be used in the join to the spatial table with in the SELECT part of the SQL statement. You are provided with an option to hide the fields used in the Data source - Map Relationships form.

The SELECT part of the SQL must contain the fields you wish to join to the spatial table with. The following rules apply when creating a JDBC based query:

 

A basic select statement has the following form:

select <fields> from mydb.myschema.mytable

The SELECT part of the SQL must contain the fields you wish to join to the spatial table with. There are four possible SQL scenarios that can occur when using JDBC based queries.

 

1. The simplest is selecting data from the same RDBMS spatial table used for display on the map. In this case you must select a field that has unique values and does not contain null values. If you are not sure what the field name is you can use SELECT *. An example of the SELECT part of the SQL is

‘select objectid, field1, field2 from mydb.myschema.mytable’

where <objectid> is the field used to connect with the map layer in the Data source - Map Relationships form.

 

2. Another simple way is selecting data from one table that is not the same spatial table used for display on the map. In this case you must select a field with values that equal the field values in the spatial table. If you are not sure what the field name is you can use SELECT *. An example of the SELECT part of the SQL is :

select someKeyId, field1, field2 from mydb.myschema.mytable

where <someKeyId> is the field used to connect with the map layer in the Data source - Map Relationships form.

 

3. Selecting from multiple tables is more complex. If you are selecting data from multiple tables, one being the same spatial table used for display on the map, you must select a field in the spatial table that is unique to the table. If you are not sure what the field name is you can use SELECT *. If the multiple tables contain matching field names then they cannot be ambiguously defined. They must be aliased with the table identifier. An example of the SQL with unique field names

select objectid field1, field2 from ArcSDETable a join AttributeTable b on a.objectid = b. someKeyId

where objectid is the field used to connect with the map layer in the Data source - Map Relationships form. An example of the SQL without unique field names is

select a.objectid as ‘a.ObjectId’, b.objectid as ‘b.ObjectId’, a.field1, b.field2 from ArcSDETable a join AttributesTable b on a.objectid = b.objectid

where <a.ObjectId> is the field used to connect with the map layer in the Data source - Map Relationships form.

 

4. Selecting from multiple tables that do not include the same ArcSDE table used for display on the map is similar to example 3. You still must select a field with values that are equal to field values in the ArcSDE table. If you are not sure what the field name is you can use SELECT *. If the tables contain matching field names then they cannot be ambiguously defined. They must be aliased with the table identifier. An example of the SQL with unique field names :

select someKeyId, field1, field2 from AttributeTable a join AttributeTable b on a.someKeyId = b.anotherKeyId

where <someKeyId> is the field used to connect with the map layer in the Data source - Map Relationships form. An example of the SQL without unique field names is

select a.someKeyId as ‘a.someKeyId’, b.someKeyId as ‘b.someKeyId’, a.field1, b.field2 from AttributesTable a join AttributesTable b on a.someKeyId = b.someKeyId

where <a.someKeyId> is the field used to connect with the map layer in the Data source - Map Relationships form.

When selecting data from multiple tables they must be inner (left) joins. If you do not specify a join type then it will be an inner join by default.


JDBC Based Query Rules