CODEWITHSUNDEEP
sql-server-2008selectwhere-clause
Deon
Deon

Reputation: 213

SQL Select with multiple AND and OR's

I'm sitting with a logic issues and I'm just confusing myself more and more.

Example table

ID | CustID | Task | Confirmed | Cancelled | Completed

1 | 20 | text | 1 | 0 | 0
2 | 20 | text | 1 | 1 | 0
3 | 20 | text | 1 | 0 | 1
4 | 17 | text | 0 | 1 | 0
5 | 17 | text | 1 | 0 | 0
6 | 20 | text | 1 | NULL | NULL
7 | 20 | text | 0 | 1 | NULL

I wish to select all tasks for custID = 20 and are confirmed=1. If the task is cancelled=1 or completed=1, do not return the record.

In this case, returned rows would be 1 and 6.

My query thus far is

SELECT *
FROM table1
WHERE    
 (CustID = 20 AND Confirmed = 1 AND Cancelled <> 1 AND Completed <> 1) OR
 (CustID = 20 AND Confirmed = 1 AND Cancelled = 1 AND Completed <> 1) OR
 (CustID = 20 AND Confirmed = 1 AND Cancelled <> 1 AND Completed = 1)

Any help would be greatly appreciated.

Upvotes: 8

Views: 85693

Answers (3)

user3205591
user3205591

Reputation: 1

SELECT * FROM thk_basvuru WHERE thk_status in (8,23,33) or thk_olo_status not in (4,6,9,11,12)

Upvotes: -2

paxdiablo
paxdiablo

Reputation: 882686

You can simplify that to:

SELECT * FROM table1
WHERE CustID = 20
  AND Confirmed = 1
  AND (Cancelled <> 1 OR Cancelled IS NULL)
  AND (Completed <> 1 OR Completed IS NULL)

You have to be careful (and very explicit) when comparing real values with NULLs, since equalities and inequalities both exclude them.

Upvotes: 24

TheHe
TheHe

Reputation: 2972

SELECT *
FROM table1
WHERE    
    CustID = 20 AND Confirmed = 1 AND NOT (Cancelled = 1 OR Completed = 1)

Upvotes: 3

Related Questions