help with query

[stamatis]

Hi all,
I’m trying to convert an existing project from a relational database.

I’m hoping I can get some help constructing a query that’s fairly straightforward in SQL...

In this scenario there are 3 tables:
- table 1 has a list of dates
- table 2 has encounter data that includes a foreign key for person and links to table 1 by date (table1.date = table2.date)
- table 3 has person data and links to table 2 with personID (table2.personID = table3.personID)

So basically selecting a date in from table 1 should retrieve all encounters in table 2 with the same date and in each retrieved result, the personID foreign key should retrieve person data from table 3 when presenting the data.

in SQL i’d just use a SELECT with JOIN statements to retrieve this in one go.

Is this feasible with liveCloud? Or would i need to construct an array running 2 separate queries?
Alternatively, should the data structure change to include all person data in the encounter table for example?

grateful for any pointers/suggestions...

[efrain.c]

Hi Stam,

The following code will work with your existing data model. It will give you an array with the data for the people that match the criteria.

Code:

local tEncounterDataQueryA, tSelectedDate, tForeignKeys, tPersonDataA
   
put cdb_Query("date", "=", tSelectedDate, "encounterDataTable", "cloud", "recordData") into tEncounterDataQueryA
   
repeat for each key xRecordID in tEncounterDataQueryA
     put tEncounterDataQueryA[xRecordID]["foreignKey"] & lf after tForeignKeys
end repeat
delete char -1 of tForeignKeys
   
put cdb_read("personDataTable", tForeignKeys, "cloud") into tPersonDataA

Let me know if you have any questions.

[stamatis]

Hi Stam,

The following code will work with your existing data model. It will give you an array with the data for the people that match the criteria.

Code:

local tEncounterDataQueryA, tSelectedDate, tForeignKeys, tPersonDataA
   
put cdb_Query("date", "=", tSelectedDate, "encounterDataTable", "cloud", "recordData") into tEncounterDataQueryA
   
repeat for each key xRecordID in tEncounterDataQueryA
     put tEncounterDataQueryA[xRecordID]["foreignKey"] & lf after tForeignKeys
end repeat
delete char -1 of tForeignKeys
   
put cdb_read("personDataTable", tForeignKeys, "cloud") into tPersonDataA

Let me know if you have any questions.

Thanks Efrain - that pretty much matches what i was planning to do (i.e. run two different queries).

Is there a worthwhile speed benefit to changning the data model to include all data in a single table do you think?
Or would any benefit be too negligible to be worth the trouble?

[efrain.c]

The speed benefits will vary depending on factors like network speed and physical location relative to the servers but it should be roughly by a factor of 2 since it'll be 1 network call instead of 2.

If this is an action that is performed often, you will see a worthwhile speed benefit if you sync the data from the cloud to the device on startup and run local queries instead of running cloud queries.