WQL and T-SQL - different query results

Question

Greetings,

We have a collection that consists of computers that have the software 'Carbon Black' installed. The actual WQL is listed below.
We also have a collection of computers that DO NOT have the software 'Carbon Black' installed. This collection simply 'includes' all windows 10 clients and 'excludes' computers in the 'installed' collection.
What is weird is that there are a few computers that appear in the 'not installed' collection that clearly match the 'installed' query criteria. I have inspected the hardware inventory of those computers and they have the exact string being searched for in the correct attribute class.
What is weirder is that I have taken the WQL and simply converted it to T-SQL - e.g. changing SMS_R_SYSTEM to V_R_SYSTEM and changing attributes e.g. Name to Name0.
When I run that T-SQL query directly against the database it finds that computer that was in the 'not installed' collection.
IOW WQL does not find it but T-SQL does. How could this be?
WQL below:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Cb Protection Agent" or SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = "Cb Protection Agent" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Carbon Black%"

Answer 1

Thanks Sherry! Honoured that you have replied. Followed you for years!
I have mixed them because not only have a few machines not had the "Installed Applications" node but a few have not had the "Installed Software" node.
I just keep reinstalling the client on those computers.

Answer 2

The thing that still puzzles me is that even though the client on particular computer may be playing up, its the fact that the SQL query listed above returns a positive result but the WQL does not. That's not the client, that's all about SQL data.