CODEWITHSUNDEEP
mysql
stefanhalbeisen
stefanhalbeisen

Reputation: 123

MySQL query delivers unwanted data

I am trying to search through my database with this query: 

SELECT jobunique, jobtypeunique, joblat, joblng FROM job WHERE

        jobdeleted = 0

        AND

        jobpayed = 1

        AND

        (LOWER(jobtitle) LIKE '%taxi%'
        OR LOWER(jobmaincategory) LIKE '%taxi%'
        OR LOWER(jobsubcategory) LIKE '%taxi%'
        OR LOWER(jobdescription) LIKE '%taxi%'
        OR LOWER(jobcountry) LIKE '%taxi%')

        AND

        (
            jobbusiness = 1 AND jobstartdate <= CURDATE() AND jobenddate >= CURDATE()
        ) OR
        ( 
            jobbusiness = 0 AND jobfixedstartdate = 0 AND jobfixedenddate = 0 AND jobdateadded >= (CURDATE()-INTERVAL 60 DAY) 
        ) OR
        (
            jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 0 AND jobstartdate >= CURDATE()
        ) OR
        (
            jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 1 AND jobenddate <= CURDATE()
        )

But regardless of whether there is "taxi" in one of the fields, I receive all the rows where jobbusiness = 0. Can someone please explain to me why? Thank you

Upvotes: 0

Views: 44

Answers (1)

Nick
Nick

Reputation: 147166

You need an extra set of parentheses around your final group of ORed conditions otherwise the last 3 conditions just get ORed independently of the rest of the conditions. Note that you don't need () around ANDed conditions as AND takes precedence over OR anyway. Changing the last part of your WHERE condition to this should fix the problem:

AND (
        jobbusiness = 1 AND jobstartdate <= CURDATE() AND jobenddate >= CURDATE()
    OR
        jobbusiness = 0 AND jobfixedstartdate = 0 AND jobfixedenddate = 0 AND jobdateadded >= (CURDATE()-INTERVAL 60 DAY) 
    OR
        jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 0 AND jobstartdate >= CURDATE()
    OR
        jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 1 AND jobenddate <= CURDATE()
)

Upvotes: 4

Related Questions