CODEWITHSUNDEEP
mysqlsql
911Rapid
911Rapid

Reputation: 199

Inaccurate results with MySQL Subquery

This is the query created by my PHP script . The query executes successfully without any errors, but with results i am not expecting. I want the rows in the result of this query to contain all the three keywords (3D,design,PAG). But, Right now it returns results even if one key word is found. How can the query be Modified ?

SELECT ID
FROM  `catiascripts` 
WHERE  `ID` LIKE  '%3D%'
OR  `ScriptName` LIKE  '%3D%'
OR  `ScriptApplication` LIKE  '%3D%'
OR  `ScriptDescription` LIKE  '%3D%'
OR  `Customer` LIKE  '%3D%'
AND ID
IN (

SELECT ID
FROM  `catiascripts` 
WHERE  `ID` LIKE  '%design%'
OR  `ScriptName` LIKE  '%design%'
OR  `ScriptApplication` LIKE  '%design%'
OR  `ScriptDescription` LIKE  '%design%'
OR  `Customer` LIKE  '%design%'
)
AND ID
IN (

SELECT ID
FROM  `catiascripts` 
WHERE  `ID` LIKE  '%PAG%'
OR  `ScriptName` LIKE  '%PAG%'
OR  `ScriptApplication` LIKE  '%PAG%'
OR  `ScriptDescription` LIKE  '%PAG%'
OR  `Customer` LIKE  '%PAG%'
)
LIMIT 0 , 30

Upvotes: 0

Views: 28

Answers (2)

shawnt00
shawnt00

Reputation: 17915

SELECT ID
FROM `catiascripts` 
WHERE (
       `ID` LIKE '%3D%'
    OR `ScriptName` LIKE '%3D%'
    OR `ScriptApplication` LIKE '%3D%'
    OR `ScriptDescription` LIKE '%3D%'
    OR `Customer` LIKE '%3D%'
)
AND (
       `ID` LIKE '%design%'
    OR `ScriptName` LIKE '%design%'
    OR `ScriptApplication` LIKE '%design%'
    OR `ScriptDescription` LIKE '%design%'
    OR `Customer` LIKE '%design%'
)
AND (
       `ID` LIKE '%PAG%'
    OR `ScriptName` LIKE '%PAG%'
    OR `ScriptApplication` LIKE '%PAG%'
    OR `ScriptDescription` LIKE '%PAG%'
    OR `Customer` LIKE '%PAG%'
)
LIMIT 0 , 30

Upvotes: 1

Marc B
Marc B

Reputation: 360662

You need brackets on your and/or expression. You're violating MySQL's order-of-operations, therefore you must enforce your own with appropriate ().

and has a higher priority than or, so your query:

SELECT   p OR q OR r AND x OR y OR z ...

is being executed as

SELECT p OR q OR (r AND x) OR y OR Z

You want

SELECT (p OR q OR r) AND (x OR y OR Z) AND (...)

Relevant doc: https://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

Upvotes: 3

Related Questions