OR affecting the AND clause?
I am creating a SQL statement that searches the database for keywords in specific columns. I need a query to return records that match the criteria for keyword AND AND keyword2. This worked well, however I needed to allow the query for keywords from multiple columns. After adding an OR clause, it was not possible to get a query to return results for records for which there are two keywords and not just one keyword.
Why OR clause affecting the AND clause?
How can I revise this operator so that both keywords can be used to get hit as well as looking for the three specified columns?
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%'
source to share
AND
has a higher priority than OR
. If you want to create a condition that logically says "Keyword 1 matches any of these columns, and keywrod2 matches any of these columns," you need to surround each of the arguments AND
with parentheses to avoid its precedence:
(BTW, the kurtosis of the description in the picklist can be simplified 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%')
source to share
Try the following:
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 instead ISNULL
( coalesce
also worked) and added parentheses to your logic.
source to share
You need to use parentheses for your logic to 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%'
)
source to share
I suggest you 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%'
source to share