SQL - AND OR combination

Question

This query works

SELECT *
FROM ALL
WHERE Date >= '2017-04-04 00:00:00'
AND
CONFIDENCE <> 'Discarded'
AND 
CONTEXT <>'Home'

I want to be able to say: And (CONTEXT <> 'Home') OR (CONTEXT = 'Hospital' AND LOCAL = 1)

So that I can select all records Either from April 2017 onwards, not discarded, that have a context not equal to home

OR

From April 2017 onwards, not discarded, OR with a context = hospital and local = 1.

I'm using ArcGIS. I have tried this:

"DATE_ENTER"  >= date '2017-04-04 00:00:00'
AND
"CONFIDENCE" <> 'Discarded'
AND 
(
("CONTEXT" <> 'Home address') OR ("CONTEXT" = 'Hospital' AND "LOCAL" = 1)
)

But there are still records with Context = Hospital and Local = 0

When I test

 ("CONTEXT" <> 'Home address')

or

("CONTEXT" = 'Hospital' AND "LOCAL" = 1)

alone, it works, I'm having trouble combining the two to give me what I need.

I had a look at this SQL AND OR query

first.

for record where CONTEXT = 'Hospital' first par where you check ("CONTEXT" <> 'Home address') returns true, so it doesn't matter what is written in second or part. For more confusion please check sql truth table: https://i.stack.imgur.com/yRBgu.png — Edgars T., Dec 20 '17 at 12:24

Gordon Linoff · Answer 1 · 2017-12-20T13:11:09.443

0

I think you really want:

WHERE Date >= '2017-04-04' AND  -- time component is unnecessary
      CONFIDENCE <> 'Discarded' AND 
      CONTEXT <> 'Home' AND
      (NOT (CONTEXT = 'Hospital' AND LOCAL = 1) )

When CONTEXT = 'Hospital', then it is not equal to 'Home'. So, it still matches the condition on CONTEXT.

edited Dec 20 '17 at 13:11

answered Dec 20 '17 at 12:23

Gordon Linoff

1,242,037
58
646
786

if in 4th condition you say that CONTEXT = 'Hospital', then there is no point to have 3rd condition. – Edgars T. Dec 20 '17 at 12:52
Ok.I've tried to use 'CONTEXT <> 'Home' AND (CONTEXT = 'Hospital' AND LOCAL = 1). What happens is the one line that has Context = hospital and local =1 is returned. What I want is to first say: return all the <>home contexts. This is 400 lines. Then within this, exclude those lines where context = hospital and local = 1 (6 lines). this would leave me (counts toes) 394 lines. How do? – damo Dec 20 '17 at 12:57
this should work: CONTEXT <> 'Home' AND NOT(CONTEXT = 'Hospital' AND LOCAL = 1) Gordon almost got it right. – Edgars T. Dec 20 '17 at 13:09
This seems to work, but for what I want local = 0. I changed it to that, and it appears to give me what I want. – damo Dec 20 '17 at 13:55

score 0 · Answer 2 · answered Dec 20 '17 at 13:14

0

I think the condition should be like this :

...
AND 
(
   ("CONTEXT" <> 'Home address' AND "CONTEXT" <> 'Hospital') 
   OR ("CONTEXT" = 'Hospital' AND "LOCAL" = 1)
)

When you are using ("CONTEXT" <> 'Home address') OR ("CONTEXT" = 'Hospital' AND "LOCAL" = 1) the result you are getting But there are still records with Context = Hospital and Local = 0 is correct because of the first condition "CONTEXT" <> 'Home address'

answered Dec 20 '17 at 13:14

Md. Suman Kabir

5,243
5
25
43

This worked straight away. Thanks. I'll edit the question accordingly. – damo Dec 20 '17 at 13:58

score 0 · Answer 3 · answered Dec 20 '17 at 13:23

0

(CONTEXT NOT IN('Hospital' , 'Home') OR (CONTEXT = 'Hospital' AND LOCAL= 1))

answered Dec 20 '17 at 13:23

Joe

71
4

score 0 · Accepted Answer · answered Dec 20 '17 at 14:00

WHERE Date >= '2017-04-04' AND  -- time component is unnecessary
  CONFIDENCE <> 'Discarded' AND 
  CONTEXT <> 'Home' AND NOT(CONTEXT = 'Hospital' AND LOCAL = 0)

Works

as does

    WHERE Date >= '2017-04-04' AND  -- time component is unnecessary
              CONFIDENCE <> 'Discarded' 
AND
    (
       ("CONTEXT" <> 'Home address' AND "CONTEXT" <> 'Hospital') 
       OR ("CONTEXT" = 'Hospital' AND "LOCAL" = 1)
    )

SQL - AND OR combination

4 Answers4