SET_INTERSECTIONS function

The SET_INTERSECTIONS aggregation function takes a multi-assign attribute and constructs a set that is the intersection of all of the values from that attribute.

The syntax of the SET_INTERSECTIONS function is: 
SET_INTERSECTIONS(<multi-assign_attribute>)
where the data type of the attribute must be a set data type (such as mdex:string-set for a multi-assign string attribute).

This function can be used only in SELECT clauses.

SET_INTERSECTIONS example

In this example, Body is a multi-assign string attribute: 
RETURN results AS
SELECT SET_INTERSECTIONS(Body) AS bodyIntersection
GROUP BY WineType
ORDER BY WineType
The result of this statement might be: 
WineType        bodyIntersection
------------------------------------
| Bordeaux:  | { Silky, Tannins }  |
| Brut       | { Robust }          |
| Chardonnay | { }                 |
| Merlot     | { }                 |
| Pinot Noir | { Supple }          |
| Red        | { }                 |
| White      | { }                 |
| Zinfandel  | { Robust, Tannins } |
------------------------------------
The sets are derived as follows:
  • Bordeaux: Assigned on three records, with each record having two Body assignments of "Silky" and "Tannins". Therefore, there is an intersection among the three records and a two-element set is returned.
  • Brut: Assigned on two records, with each record having one Body assignment of "Robust". Therefore, there is an intersection between the two records and a one-element set is returned.
  • Chardonnay: Assigned on two records, but neither record has a Body assignment. Therefore, there is no intersection between the two records (because there are no values to compare) and the empty set is returned.
  • Merlot: Assigned on two records, with one record having one Body assignment of "Fruity" and the other record having no Body assignment. Therefore, there is no intersection between the two records and the empty set is returned.
  • Pinot Noir: Assigned on only one record, which has one Body assignment of "Supple". Therefore, there is an intersection on that record.
  • Red: Assigned on eight records, with six records having two Body assignments of "Silky" and "Tannins", one record with two Body assignments of "Robust" and "Tannins", and the eighth record with one Body assignment of "Robust". Therefore, there is no intersection among the eight records and the empty set is returned.
  • White: Assigned on four records, with the first record having two Body assignments of "Fresh" and "Robust", the second record with two Body assignments of "Firm" and "Robust", and the third and fourth records with no Body assignments. Therefore, there is no intersection among the four records and the empty set is returned.
  • Zinfandel: Assigned on only one record with two Body assignments of "Robust" and "Tannins". Therefore, there is an intersection on that record and a two-element set is returned.