Spatial View, one to many relationships, unexpected results

Discussion created by bfisher on May 1, 2012
Latest reply on May 1, 2012 by vangelo-esristaff
I have a created a spatial view from the following input:  poly fc (unique ParcelID) & attribute table (not unique ParcelID resulting in a one 2 many relationship).

Syntax below:

sdetable -o create_view -T LR_Land_Owners_SV_noOLWands -t LR_Land_Owners,LR_Stakeholders -c "LR_Land_Owners.OBJECTID,LR_Land_Owners.PARCELID,LR_Land_Owners.Shape,LR_Stakeholders.NATURE,LR_Stakeholders.Stakeholder,LR_Stakeholders.Frequency,LR_Stakeholders.multi_stakeholders,LR_Stakeholders.order_limits" -a "OBJECTID,PARCELID,SHAPE,NATURE,STAKEHOLDER,FREQUENCY,MULTI_STAKEHOLDERS,ORDER_LIMITS" -w "LR_Land_Owners.PARCELID = LR_Stakeholders.PARCELID AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'" -u XXXXX -p XXXXX -i sde:sqlserver:database_instance -D LTTGISV

The view is created as i would expect, the results are as i would expect whereby i get a list of stakeholders as defined by above. 

However, the issue being that when i review the data in ArcGIS desktop, whereby i run a defn query against the stakeholder attribute, I get values for the stackholder attribute that SHOULD not be listed.  I can understand why its happening (the join on PARCELID is resulting in first 'random' record where the one2many relationship exist).  Interestingly if you run query against the attribute table, it will only let see the unique records (for stakeholder) as defined (restricted) by your query defn.  Would one agree its a bug? 

The issue is avoided if i put the stakeholder query within the spatial view query definition but I'm trying to avoid this since this results in unique spatial views for each of my stakeholders (whereas if i keep the stakeholder query in the mxd (query definition) it enables me to have a single 'stakeholder' spatial view). 

I note 'identifying' the parcels in question seems to always return the correct value for stakeholder; as does my web app which is dependent on the spatial view.

I have played with the join type in the view (database level) but with no success. 

Interested in any feedback anyone has to give as while i think i understand what is happening, it seems someone inconsistent.