Combining Data from Multiple Tables
More complex filter expressions can involve combining data from two or more database tables into a single table. Join statements enable you to do this. To join two or more tables, there needs to be data that is common to both tables. In filter expressions, this is typically the NODENAME field. This common field in each table is referenced in a Join statement, as shown in the following examples.
This filter expression joins the NODES table with the PKGRPTDB table, which stores the software inventory data, to identify all Clients with WinZip installed:
SELECT * FROM NODES INNER JOIN PKGRPTDB ON PKGRPTDB.NODENAME = NODES.NODENAME WHERE PKGRPTDB.PACKAGE LIKE 'WinZip%' AND PKGRPTDB.STATUS NOT LIKE 'Deleted'
This example uses Client Properties data to identify the PCs in a specific location:
SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME = '$LOCATION' AND NODEPROP.PROPVALUE LIKE 'upstairs%'