WHERE OR but not both

Question

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.

hi, since you are developing SQL here - your question specification is a little confusing - your list of bullet points is MANAGEResque - it doesn't tell us if you want one, some, or all of the conditions - there are best guesses available - sorry to tell you off about it — Cato, Nov 02 '16 at 15:51

score 1 · Answer 1 · answered Nov 02 '16 at 14:56

1

If I understood you correctly you can basically do this:

WHERE NOT(1 AND 2) AND (1 OR 2)

answered Nov 02 '16 at 14:56

sagi

40,026
6
59
84

Pரதீப் · Answer 2 · 2016-11-02T15:05:58.103

Move the filter to HAVING clause and do conditional count

Here is one way to do it

SELECT Devices.DeviceName
FROM   Devices
       JOIN CustInv_ObjType_6121 BCMModulesVersions
         ON Devices.DeviceID = BCMModulesVersions.DeviceID
       JOIN InventoryIntegrationData InventoryUpdate
         ON Devices.DeviceID = InventoryUpdate.DeviceID
WHERE  ( InventoryUpdate.IntegrationDate IS NOT NULL )
       AND Devices.TopologyType IN ( '_DB_DEVTYPE_CLIENT_', '_DB_DEVTYPE_RELAY_' )
GROUP  BY Devices.DeviceName
HAVING Count(CASE
               WHEN BCMModulesVersions.InstanceName IN ( 'PatchManagementPremium', 'RemoteControl' ) THEN 1
             END) = 0

JamieD77 · Answer 3 · 2016-11-02T15:49:53.407

I think you might be able to use NOT EXISTS here.

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

if you want to exclude only when both values exists you can use.

SELECT DISTINCT
        Devices.DeviceName
FROM    Devices
        JOIN InventoryIntegrationData InventoryUpdate ON Devices.DeviceID = InventoryUpdate.DeviceID
WHERE   InventoryUpdate.IntegrationDate IS NOT NULL
        AND Devices.TopologyType IN ('_DB_DEVTYPE_CLIENT_','_DB_DEVTYPE_RELAY_')
        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;

what if he does have 'RemoteControl', but not 'PatchManagementPremium' - does he want to see that record? I understood he does want to see that record - just not if he has both — Cato, Nov 02 '16 at 15:42
@AndrewDeighton i've read the question 8 times and honestly can't tell you what exactly they're asking for. lol.. but i think you're right — JamieD77, Nov 02 '16 at 15:47

score 0 · Answer 4 · answered Nov 02 '16 at 15:39

i think you are saying that one or both of the two cases is missing (so they are both present is excluded)

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  

    (NOT EXISTS (SELECT 0 FROM CustInv_ObjType_6121 X WHERE X.InstanceName IN('RemoteControl'))
        OR
     NOT EXISTS (SELECT 0 FROM CustInv_ObjType_6121 Y WHERE Y.InstanceName IN('PatchManagementPremium'))
    )
    AND (InventoryUpdate.IntegrationDate IS NOT NULL)
    AND Devices.TopologyType IN ('_DB_DEVTYPE_CLIENT_', '_DB_DEVTYPE_RELAY_')
ORDER BY 
    Devices.DeviceName ASC;

score 0 · Accepted Answer · answered Nov 07 '16 at 17:57

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!

I supposed that my modifications could be improved, though but it does the job! :) — druid, Nov 07 '16 at 17:58

WHERE OR but not both

5 Answers5