1:M query

[SandraDema]

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?

[JayantaPoddar]

Did you try creating a relationship class​?

[JayantaPoddar]

For more details on Join and Relate tables,

About joining and relating tables

[SandraDema]

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?

[JayantaPoddar]

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

[SandraDema]

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

[ToddBlanchette]

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'

[SandraDema]

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.

[JayantaPoddar]

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

[JayantaPoddar]

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.