CODEWITHSUNDEEP
sqlsql-serverselector-operatorand-operator
user3839756
user3839756

Reputation: 813

OR clause affecting an AND clause?

I am creating a SQL statement that searches a database for keywords in specific columns. I need the query to return records that meet criteria for keyword1 AND keyword2. This was working well however I needed to allow the keywords to be queried from multiple columns. After adding the OR Clause have been unable to get the query to return results for records that have a hit for both keywords not just one keyword.

Why is the OR Clause affecting the AND Clause?

How can I revise this statement to allow both of the keywords to be needed to get a hit while also searching the 3 columns specified?

The statement: 

SELECT CASE WHEN t1.longdesc IS NULL THEN t1.desc 
WHEN t1.longdesc IS NOT NULL THEN t1.longdesc END AS 'description', 
t1.upc 
FROM Items t1 
LEFT JOIN Suppliers t2 ON t1.supplier = t2.supplier_no 
LEFT JOIN Sections t3 ON t1.Section = t3.section_no 
LEFT JOIN Groups t4 on t1.group = t4.group 
WHERE desc LIKE '%keyword1%' 
OR Item_code LIKE '%keyword1%' 
OR certify_code LIKE '%keyword1%' 
AND desc LIKE '%keyword2%' 
OR Item_code LIKE '%keyword2%' 
OR certify_code LIKE '%keyword2%'

Upvotes: 3

Views: 217

Answers (5)

shA.t
shA.t

Reputation: 16958

I suggest you to avoid using OR as a performance issue, and use ISNULL(), So you can use this:

SELECT 
    ISNULL(t1.longdesc, t1.desc) AS 'description', 
    t1.upc 
FROM 
    Items t1 
LEFT JOIN 
    Suppliers t2 ON t1.supplier = t2.supplier_no 
LEFT JOIN 
    Sections t3 ON t1.Section = t3.section_no 
LEFT JOIN 
    Groups t4 on t1.group = t4.group 
WHERE 
    (desc + ':' + Item_code + ':' + certify_code) LIKE '%keyword1%' 
AND (desc + ':' + Item_code + ':' + certify_code) LIKE '%keyword2%'

Upvotes: 1

JohnS
JohnS

Reputation: 2052

You need to use brackets to make your logic work. See below:

 SELECT
  CASE WHEN t1.longdesc IS NULL THEN t1.[desc]
       WHEN t1.longdesc IS NOT NULL THEN t1.longdesc
  END AS 'description',
  t1.upc
 FROM
  Items t1
  LEFT JOIN Suppliers t2
    ON t1.supplier = t2.supplier_no
  LEFT JOIN Sections t3
    ON t1.Section = t3.section_no
  LEFT JOIN Groups t4
    ON t1.[group] = t4.[group]
 WHERE
  (
    [desc] LIKE '%keyword1%'
    OR Item_code LIKE '%keyword1%'
    OR certify_code LIKE '%keyword1%'
  )
  AND (
        [desc] LIKE '%keyword2%'
        OR Item_code LIKE '%keyword2%'
        OR certify_code LIKE '%keyword2%'
      )

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Change your WHERE part to be like below by parenthesizing them using ()

WHERE (
[desc] LIKE '%keyword1%' 
OR Item_code LIKE '%keyword1%' 
OR certify_code LIKE '%keyword1%' 
)
AND 
(
[desc] LIKE '%keyword2%' 
OR Item_code LIKE '%keyword2%' 
OR certify_code LIKE '%keyword2%'
)

Upvotes: 1

Mureinik
Mureinik

Reputation: 311338

AND has higher precedence than OR. If you want to create a condition which logically says "keyword1 is matched on any of these columns and keywrod2 is matches on any of these columns", you'll need to surround each of ANDs arguments with parentheses to avoid it taking precedence:

(BTW, the description exression in the select list could be simplified by using coalesce) 

SELECT COALESCE (t1.longdesc, t1.desc) AS description, t1.upc 
FROM Items t1
LEFT JOIN Suppliers t2 ON t1.supplier = t2.supplier_no 
LEFT JOIN Sections t3 ON t1.Section = t3.section_no 
LEFT JOIN Groups t4 on t1.group = t4.group 
WHERE (desc LIKE '%keyword1%' OR 
       Item_code LIKE '%keyword1%' OR 
       certify_code LIKE '%keyword1%') AND 
      (desc LIKE '%keyword2%' OR 
       Item_code LIKE '%keyword2%' OR 
       certify_code LIKE '%keyword2%')

Upvotes: 3

APH
APH

Reputation: 4154

Try this:

SELECT 
    ISNULL(t1.longdesc, t1.[desc]) AS 'description'
    , t1.upc 
FROM Items t1 
LEFT JOIN Suppliers t2 
    ON t1.supplier = t2.supplier_no 
LEFT JOIN Sections t3 
    ON t1.Section = t3.section_no 
LEFT JOIN Groups t4 
    on t1.group = t4.group 
WHERE 
    ([desc] LIKE '%keyword1%' 
        OR Item_code LIKE '%keyword1%' 
        OR certify_code LIKE '%keyword1%') 
    AND (desc LIKE '%keyword2%' 
        OR Item_code LIKE '%keyword2%' 
        OR certify_code LIKE '%keyword2%')

I cleaned up your case statement to use ISNULL instead (coalesce would also work), and added parentheses to your where logic.

Upvotes: 1

Related Questions