Aggregate Result Set

[Former Member]

I have a schema as follows:

TABLE1

-

---

ID

Name

Table2Ids

TABLE2

-

---

ID

Name

Where TABLE1.Table2Ids is a foreign key to TABLE2.ID. Is there any way of getting the following resultset?

TABLE1.Name | TABLE2.Name

Thanks,

Nehal

[Former Member · Accepted solution]

Hi Nehal,

What you are asking is automatically supported in MDM if the "Name" field in TABLE2 is marked as a "Display Field". Then, when you get a ResultSet for TABLE1, the GetValue function for the TABLE1.Table2Ids field will return the Name from TABLE2.

To make this clearer here is some pseudo code based on the Java or COM API's:

/* Step 1 - build a ResultSetDefinition on TABLE1 and add the required fields
*/
ResultSetDefinition table1_rsd = new ResultSet();
table1_rsd .SetTable("TABLE1");
table1_rsd .AddField("Name");
table1_rsd .AddField("Table2Ids");

/* Step 2 - Get a ResultSet */
ResultSet rs = catalog.GetResultSet(someSearch, table1_rsd,....);

/* Step 3 - Loop through the ResultSet and view the record Values 
*/
for(int i = 0; i < rs.GetRecordCount(); i++)
{
    String <b>nameFromTable1</b> = rs.GetValueAt(i, "Name").GetStringValue();
    String nameFromTable2 = rs.GetValueAt(i, "Table2Ids").GetStringValue();
}

[Former Member]

Hello Nehal:

I'd personally recomend getting two result sets (one for each table) and then joining them together... is there an special requirement of why you want to do that? or are you trying to emulate joins that can be done on relational databases?

I assume the latter, since you mention "Foreign Key", do you mean a lookup table, or a "Keymapping"

Regards

Alejandro