Administer > Database administration > Data persistence > Queries > Cross-table join query improvements > Example: Handle a one-to-many relationship

Example: Handle a one-to-many relationship

This query shows all Incidents that have an associated open interaction relation.

Note All previous examples show a many-to-one relationship; that is, multiple rows in the first table are associated to the same row in another table. For example, multiple Incidents have the same owner. In this case, the query returns only one row for each row in the Incident table. In this example, the query returns more than one row for each row in the first table. Therefore, the results of this query may resemble the following:

Incident Interaction
IM10003 SD10060
IM10003 SD10033
IM10003 SD10003
IM10002 SD10006
IM10002 SD10002 
var file = new SCFile("probsummary");
var sql = "SELECT ta01.*, ta03.incident.id AS iid FROM probsummary ta01 LEFT OUTER JOIN screlation ta02 LEFT OUTER JOIN incidents ta03 ON  ( ta02.depend = ta03.incident.id )  ON  ( ta01.number = ta02.source )  WHERE ( ta03.open#\"Open\" )";
var success = file.doSelect(sql);
while (success == RC_SUCCESS) 
{
    print(file.number + " " + file.iid);
    success = file.getNext();
}