The queries are not the same, hence this is why you are getting
different results. For example notice that within SQL query you are
telling it to count all IP address and you are not doing the same within
WQL.
This query will show you the duplicates. (Look at the IP field)
select
*
from
v_r_system as a
inner join v_RA_System_SMSAssignedSites as b on
a.ResourceID=b.resourceID
inner join v_RA_System_IPAddresses as c on a.ResourceID=c.resourceID
where
a.Obsolete0=0
and (b.SMS_Assigned_sites0='MIM'
or a.Name0 like 'ttt%'
or a.Name0 like 'mtt%'
or c.IP_addresses0 like '172.16%')
ORDER BY
a.ResourceID
"Jaison Jose" <JaisonJose@discussions.microsoft.com> wrote in message
news:2FCEDB97-1981-40AC-8B26-404D49B05EE8@microsoft.com:
> Hi All,
>
> I have a collection created with the following code as a criteria.
>
> select
> SMS_R_System.ResourceID,SMS_R_System.ResourceType, SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier ,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_Syst em.Client
> from SMS_R_System where SMSAssignedSites = "MIM" or Name like "ttt%" or Name
> like "mtt%" or IPSubnets like "172.16.%" order by Name
>
> If i do a 'show count' i get,
>
> Total No of resources in collection=760
> Total number of clients in collection= 709
> Total no of non-obsolete clients in collection=709
>
>
> I have written a very similar SQL code in Query Analyzer, it vl look like
> this:
>
> select count(*) from v_r_system as a inner join v_RA_System_SMSAssignedSites
> as b on a.ResourceID=b.resourceID
> inner join v_RA_System_IPAddresses as c on a.ResourceID=c.resourceID where
> a.Obsolete0=0 and (b.SMS_Assigned_sites0='MIM' or a.Name0 like 'ttt%'
> or a.Name0 like 'mtt%' or c.IP_addresses0 like '172.16%')
>
> This does return a count of 763.
>
> If you check this code, you can see that i have already given the condition
> to avoid obsoletes. Then i should get the same count as my collection, which
> should be 709 right? but i still get 763!!
>
> Is there any tuning can be done in my SQL code so that i get the same count
> of non-obsolete machines as my collection??
>
>
> Appreciate any help.
>
> Many thanks guys
>
> Jaison
--
Garth Jones
Chief Architect
www.Enhansoft.com
Enhancing Your Business