Collection Vs SQL tables!!

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

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

Hi Garth,

How are you?

Thanks a lot!
I did manage to write a correct piece of code....

select count(distinct a.resourceid)as [All Internet Systems] from v_r_system
as a inner join v_RA_System_SMSAssignedSites as b on
a.ResourceID=b.resourceID
inner join v_RA_System_IPSubnets 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_subnets0 like '172.16%')

it returns me 709 records!! :-)

JJ



"Garth" wrote:

> 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
>
>
>