A subset query is made up of two parts, first the positive query and then the negative query. This might seem dumb but in order to ensure that you get the results that you are looking for you need to exclude all PCs with the positive.
Let’s use the following table for all of our examples. This table is comprised of 4 PC add/remove programs (ARP).
|SCCM client||SCCM client||SCCM client||SMS 2k3 client|
|Adobe Reader 7.1||MS Project 2k||Visio 12||Adobe Reader 8.0|
|Visio 12||CorelDraw||ITunes||MS Project 2k|
Use a pseudo code if I write my query like this,
Find all PC where ARP not like “Visio 12”
A lot of people would expect to get PC2 and PC4 returned but that is not the case. Think about it. For PC1 is the first ARP line like “Visio 12”? No it is not therefore it would be added to result set. The same is also true for PC 4.
Now let look at the positive method, Use a pseudo code if I write my query like this,
Find all PC where ARP like “Visio 12”
In this case we would expect that PC1 and PC3 to be returned and you would be correct. So you ask but I want to know all PCs without “Visio 12” how do I do that?
This brings us to the negative and subset part of the query. We know all PCs that have “Visio 12” so now let find all that don’t. Use a pseudo code if I write my query like this,
Find all PC where PC is not in (Find all PC where ARP like “Visio 12”)
First this query will find all PCs with “Visio 12” then it will compare this to all PC and only display those PC which are not in the first query. Hence we get PC2 and PC4.
Now you are asking yourself what use is it to know the background of how I get what I want! What I need is some “real world examples” to play with.
Here are examples for:
SQL (2 days from now)