Search code examples
sqlsql-servertoto

WHERE <Cond> OR <Cond> but not both

I'm trying to find Devices for which:

  • "PatchManagementPremium" doesn't exist in the column instancename
  • "RemoteControl" doesn't exist in the column instancename
  • Both entries do not exist in the column instancename

But I want to exclude all the results where both entries are true, which I did not manage to do until now. No matter what I tried, when 1 AND 2 are true then it acts as it was false...

SELECT DISTINCT 
    Devices.DeviceName
FROM
    Devices
LEFT OUTER JOIN 
    CustInv_ObjType_6121 BCMModulesVersions ON Devices.DeviceID = BCMModulesVersions.DeviceID
LEFT OUTER JOIN 
    InventoryIntegrationData InventoryUpdate ON Devices.DeviceID = InventoryUpdate.DeviceID
WHERE  
    ((BCMModulesVersions.InstanceName NOT IN ('PatchManagementPremium', 'RemoteControl'))
    AND (InventoryUpdate.IntegrationDate IS NOT NULL)
    AND Devices.TopologyType IN ('_DB_DEVTYPE_CLIENT_', '_DB_DEVTYPE_RELAY_'))
ORDER BY 
    Devices.DeviceName ASC;

To be clearer: I support an application on which several modules can be loaded or not. This information is stored in the database. When a module is loaded you will find its name (remotecontrol, patchmanagementpremium etc) in the column instancename for that device.

I want to list all devices on which the module remotecontrol is not loaded, or the module patch is not loaded or both modules are not loaded.

If both entries are loaded the devicename should not be in the output of the query.

Solution

  • I found the solution thanks to JamieD77! :)

    SELECT DISTINCT
        Devices.DeviceName
FROM    Devices
        JOIN InventoryIntegrationData InventoryUpdate ON Devices.DeviceID = InventoryUpdate.DeviceID
        JOIN CustInv_ObjType_6121 BCMModulesVersions ON Devices.DeviceID = BCMModulesVersions.DeviceID
WHERE   InventoryUpdate.IntegrationDate IS NOT NULL
        AND Devices.TopologyType IN ('_DB_DEVTYPE_CLIENT_','_DB_DEVTYPE_RELAY_')
        AND BCMModulesVersions.InstanceName NOT IN ('PatchManagement','RemoteControl')
        AND 2 > ( SELECT COUNT(DISTINCT BCMModulesVersions.InstanceName)
                         FROM   CustInv_ObjType_6121 BCMModulesVersions
                         WHERE  Devices.DeviceID = BCMModulesVersions.DeviceID
                                AND BCMModulesVersions.InstanceName IN ('PatchManagementPremium','RemoteControl'))
ORDER BY Devices.DeviceName ASC;

    Thanks all for your help!