Sample Query with Exists() Function
Copy
The Exists() Function can be applied in a WHERE condition to filter Items based on the existence of related items. In essence, it’s similar to a LEFT INNER JOIN in a SQL statement. To explain the use of such a feature, refer to the basic Query Definition shown in Figure 58.
Figure 58.
This query will result in returning all Parts in the system. It’s equivalent to the following SQL statement:
SELECT FROM Part
The addition to this Query Definition shown in Figure 59 will add related Parts through the Part BOM Relationship.
Figure 59.
This query will return all Parts, including those Parts that have a Part BOM Relationship along with the related (child) Part. It’s equivalent to the following SQL statement:
SELECT FROM Part pLEFT JOIN Part BOM pb on …LEFT JOIN Part child on…
The addition to this Query Definition shown in Figure 60 will add the Condition Exists([[Relationship]] Part BOM) on the Context Part Query Item.
Figure 60.
This query will return all Parts that have a Part BOM Relationship along with the related (child) Part. It’s equivalent to the following SQL statement:
SELECT FROM Part pINNER JOIN Part BOM pb on …LEFT JOIN Part child on…
The effect is to restrict the Part Items returned for the Context Query Item to only those that have related Parts using the Part BOM relationship. Any related Query item can also have conditions applied to it. For example, using the previous example, the Part BOM Query item can have a condition that restricts Items based on a Quantity > 2. In this case, the entire Query Definition will only return Parts that have related Part BOM Items that have a Quantity of at least 3.
Figure 61.
The Exists() function can be used on any Query item with related content. Note that the Condition Editor will automatically offer valid Exists() statements. See Figure 62 for an example.
Figure 62.
In this case, beginning to type the Exi term will isolate the list of valid statements and include all valid ways to use Exists() for the selected Query item.