CODEWITHSUNDEEP
sqlsql-serversubquery
Jay C
Jay C

Reputation: 872

Combining AND & OR with Exists

I'm using MS SQL Server 2012

I've tried various combinations of AND, OR and parenthesis but I can't get this query to return the result set it should. I can get the query to exclude the rows like 'Closed%' but I can't get the OR clause to also exclude 'Name One'

The second AND OR Clause in the subquery that is excluding entries based on AccountClosedDate is working.

    SELECT 
    Distinct a.CompositeID,
    a.ClientID,
    a.ClientName,
    a.CompositeName,
    a.BranchName
    FROM  AllAccounts a
    WHERE (a.Compositename NOT LIKE 'Closed%' OR a.CompositeName <> 'Name One')
    AND EXISTS
    (
SELECT  DISTINCT CompositeID, CompositeName
FROM allaccounts c
WHERE c.CompositeID = a.CompositeID
AND (c.AccountClosedDate >= '1/1/2015' OR c.AccountClosedDate IS NULL))
ORDER BY a.CompositeName

Sample Output I want to exclude the ones that have Closed in the name or Name One

CompositeID ClientID    ClientName  CompositeName   BranchName
8801    5318    Client1 Name Two    Firm1
7087    2311    Client2 Name Three  Firm1
4817    2311    Client3 Name Four   Firm1
11163   6863    Client4 Name Five   Firm1
4031    2540    Client5 Closed3 Firm2
5079    2061    Client6 Closed2 Firrm2
5079    1149    Client7 Closed2 Firm3
5079    2249    Client8 Closed2 Firm3
5079    1873    Client9 Closed2 Firm3
5079    960 Client10    Name One    Firm3
5079    1011    Client11    Name One    Firm3
5079    1588    Client12    Name One    Firm3
4090    1594    Client13    Name 6  Firm3

enter image description here

Upvotes: 0

Views: 58

Answers (1)

Szymon
Szymon

Reputation: 43023

If you want to exclude the names based on 2 conditions described above, you need to use AND in your clause:

(a.Compositename NOT LIKE 'Closed%' AND a.CompositeName <> 'Name One')

When it checks each record, it only includes it when both conditions are met.

Let's look at a few records:

Name             AND            OR
Name Two         True           True
Closed3          False          True
Name One         False          True

If you put OR between your conditions, you are including all records that don't start with 'Closed' or are not 'Named One'. All records meet one or the other condition so all are included.

So 'Closed3' is true for the second condition which makes OR-linked clause true. Same way, Name One is true for the first one, which makes the whole clause true.

Upvotes: 1

Related Questions