I am using javascript and asp, and using ArcGIS Server services. I need to create a 1:M relationship and be able to query against the "many" table. For example:
For every house (a spatial feature), there can be multiple pets(a joined table from sql, with 0 to many entries per house).
Select * from join where pet = "dog".
I have found several ways to join the data, but none that I can query against. Any ideas?
Did you try creating a relationship class?
It doesn't appear that I can query against the destination table in a relationship class, just the origin table.
Looking at the link provided for Joining and Relating Tables, it says:
In all cases of 1:M joins, only the first matching record is joined and displayed in the layer's attribute table
I hope I'm just missing something. 1 to many relationships are a vital part of most any modern database.
Maybe there is another way to accomplish what I need?
You need to go for Relationship class.... Not Join. Join will add only the first matching record from the join table
I cannot find a method to query against the relationship fields. Do you have an example that you could share?
Hi Sandra,
Is a standard SQL query across two tables not working? Ex:
SELECT house.address, house.owner, pet.petname, pet.type FROM house JOIN pet ON house.ID=pet.ID WHERE pet.type='dog'
No. It says "field was not found"
I am able to see the related table in the identify window via arc map, so I know that the relationship is set up correctly.
After publishing the service with a related table, you will find an operation "Query Related Records" in REST. Try it.
Hi Sandra,
I should have asked earlier. Have you tried Make Query Table
Make a SQL query in it similar to
"HOUSE.HID" = "PET.HID"
Here 1 HOUSE.HID record corresponds to multiple PET.HID records.