BRONZE PARTNER:
BRONZE PARTNER:
Industry News:
| |
| |
|
|
|
|
|
| Using the subselect (NOT) query |
|
|
|
By: Rod Trent
Posted On: 6/6/2001
A Subselect query is one that you use to determine if something is NOT available. For example, if you want to find the computers where MS Access (the example below) is NOT installed, you would use a Subselect query.
To do this you have to create two queries. The first one will give you a list of everyone who has the software, the second will return a list of everyone who does not have the software. The second query relies on the Wbem Query Language (WQL) statement that comprises the first.
Create the first query:
1. Right click on Queries, select New, then Query.
2. Give your query a name...something like All Systems with Microsoft Access
3. Click on Edit Query Statement
4. In the General tab click the Add button (the yellow star).
5. In the Result Properties dialog click on the Select button.
6. Select System under Attribute Class then select the Name attribute.
7. Click OK.
8. Click OK again to close the Result Properties dialog.
9. Next select the Criteria tab and click the add button.
10. In the Criterion Properties dialog click the Select button.
11. In the Select Attribute dialog select the Attribute class Software Files and choose File Name as the Attribute.
12. Click OK.
13. Back in Criterion Properties leave the Operator set to is equal to and type msaccess.exe in the Value field.
14. Click OK to close the Criterion Properties dialog.
Now you have designed a query that locates computers that have the software installed. This base query will be used with your subselected query to find computers that do not have this software.
Create the subselected query:
1. Right click on Queries, select New, then Query.
2. Give your query a name like All Systems w/out Microsoft Access.
3. Click on Edit Query Statement.
4. In the General tab click the Add button (the yellow star).
5. In the Result Properties dialog click on the Select button.
6. Select System under Attribute Class then select the Name attribute.
7. Click OK.
8. Click OK again to close the Result Properties dialog.
9. Next select the Criteria tab and click the Add button.
10. Use the Criterion Type pull down to select Subselected values as your Criterion Type.
11. Click the Select button.
12. In the Select Attribute dialog select the Attribute class System and choose Name as the Attribute.
Note that you used System.Name as the results for your first query.
13. Click OK.
14. Back in Criterion Properties set the Operator to is not in.
15. Below that, click Browse under Subselect.
16. In the Browse Query dialog select the All Systems with Microsoft Access query that you just created.
17. Click OK.
You'll see the WQL code from your first query is pulled into your subselect statement for this query.
18. Click OK.
19. Click OK again, then one last time to save your new query.
Now you have a query that uses the results of another query (the subselected values) to create it's own result set.
Simply put, this query says Show me all the systems that are NOT in this list.
|
|
|
|
|
|
