topic Re: 1:M query in ArcGIS JavaScript Maps SDK Questions

1:M query

SandraDema — Wed, 27 May 2015 15:11:47 GMT

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?

Re: 1:M query

JayantaPoddar — Wed, 27 May 2015 15:42:30 GMT

Did you try creating a relationship class?

Re: 1:M query

JayantaPoddar — Wed, 27 May 2015 15:55:33 GMT

For more details on Join and Relate tables,

About joining and relating tables

Re: 1:M query

SandraDema — Wed, 27 May 2015 16:15:00 GMT

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?

Re: 1:M query

JayantaPoddar — Wed, 27 May 2015 16:24:17 GMT

You need to go for Relationship class.... Not Join. Join will add only the first matching record from the join table

Re: 1:M query

SandraDema — Wed, 27 May 2015 16:48:32 GMT

I cannot find a method to query against the relationship fields. Do you have an example that you could share?

Re: 1:M query

ToddBlanchette — Wed, 27 May 2015 17:02:20 GMT

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'

Re: 1:M query

SandraDema — Wed, 27 May 2015 17:30:40 GMT

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.

Re: 1:M query

JayantaPoddar — Wed, 27 May 2015 18:32:53 GMT

After publishing the service with a related table, you will find an operation "Query Related Records" in REST. Try it.

Re: 1:M query

JayantaPoddar — Wed, 27 May 2015 18:58:48 GMT

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.

Re: 1:M query

JaiSiva1 — Fri, 29 May 2015 13:23:29 GMT

As mentioned by Jayanta Poddar ,

You can query the REST Services using Query Related tables.

Ex: Query RelatedRecords Example

Query performed on the REST Service.

and the Relationship query can be performed in the js api using the "RelationshipQuery" class