CODEWITHSUNDEEP
sqlsql-serverdatabaseselectboolean-logic
Sam
Sam

Reputation: 325

SQL - Combining AND & OR

I have the following code: 

SELECT sdd.sd_doc_classification,
       sdd.sd_title,
       sdd.sd_desc,
       sdr.sd_upload_fname
FROM sdoc_docs AS sdd
INNER JOIN sdoc_revh AS sdr ON sdd.sd_id = sdr.sd_id
WHERE (sdd.sd_title LIKE '%policy%')
    OR (sdd.sd_title LIKE '%guideline%')
    AND (sdd.sd_doc_classification NOT LIKE '%\Trust Wide Policies & Guidelines\%')

However, adding "OR (sdd.sd_title LIKE '%guideline%')" has broken it and the statement now returns values that are like '%\Trust Wide Policies & Guidelines\%'.

The code runs as intended after removing the guideline example above.

Essentially I want to display anything that has an sd_title like policy or guideline and doesn't have sd_doc_classification like '%trust wide pol%'.

Upvotes: 2

Views: 115

Answers (2)

Ted
Ted

Reputation: 2585

As pointed out by Gordon and dnoeth, your parentheses are the problem. I think you actually need the following query.

SELECT sdd.sd_doc_classification,
       sdd.sd_title,
       sdd.sd_desc,
       sdr.sd_upload_fname
FROM sdoc_docs AS sdd
INNER JOIN sdoc_revh AS sdr ON sdd.sd_id = sdr.sd_id
WHERE 
    (
        sdd.sd_title LIKE '%policy%'
        OR 
        sdd.sd_title LIKE '%guideline%'
    )
    AND 
    sdd.sd_doc_classification NOT LIKE '%\Trust Wide Policies & Guidelines\%'

I find correctly tabbing out the different OR and AND clauses clearly makes them easier to visualise and understand.

Upvotes: 2

Mureinik
Mureinik

Reputation: 312259

AND has a higher precedence than OR - i.e., it's as if you'd written your query like this:

SELECT sdd.sd_doc_classification,
       sdd.sd_title,
       sdd.sd_desc,
       sdr.sd_upload_fname
FROM       sdoc_docs AS sdd
INNER JOIN sdoc_revh AS sdr ON sdd.sd_id = sdr.sd_id
WHERE      (sdd.sd_title LIKE '%policy%') OR 
           ((sdd.sd_title LIKE '%guideline%') AND 
            (sdd.sd_doc_classification NOT LIKE 
             '%\Trust Wide Policies & Guidelines\%'))

If you want to control the precedence the conditions are evaluated, you can explicitly add parentheses yourself:

SELECT sdd.sd_doc_classification,
       sdd.sd_title,
       sdd.sd_desc,
       sdr.sd_upload_fname
FROM       sdoc_docs AS sdd
INNER JOIN sdoc_revh AS sdr ON sdd.sd_id = sdr.sd_id
WHERE      ((sdd.sd_title LIKE '%policy%') OR 
            (sdd.sd_title LIKE '%guideline%')) AND 
           (sdd.sd_doc_classification NOT LIKE 
             '%\Trust Wide Policies & Guidelines\%')

Upvotes: 3

Related Questions