CODEWITHSUNDEEP
mysqlsql
user2417624
user2417624

Reputation: 693

sql query return wrong results

Why this query returns results where companyVisible is 0, even I am asking only for companies where companyVisible has value of 1?

SELECT c.companyID, c.companyDescription , c.companyName ,c.copmanyDrastiriotita, c.companyVisible
FROM company c
WHERE c.companyVisible = 1
AND c.companyDescription LIKE '%Keyword%'
OR c.companyName LIKE '%Keyword%'
OR c.copmanyDrastiriotita LIKE '%Keyword%'

Upvotes: 1

Views: 55

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Using disjunctive normal form:

SELECT c.companyID,
       c.companyDescription, c.companyName, c.copmanyDrastiriotita,
       c.companyVisible
  FROM company c
 WHERE ( c.companyVisible = 1 AND c.companyName LIKE '%Keyword%' )
       OR ( c.companyVisible = 1 AND c.companyDescription LIKE '%Keyword%' )
       OR ( c.companyVisible = 1 AND c.copmanyDrastiriotita LIKE '%Keyword%' )

Using primitive operators (c3 could be a VIEW):

SELECT c2.companyID,
       c2.companyDescription, c2.companyName, c2.copmanyDrastiriotita,
       c2.companyVisible
  FROM company c2, (
                    SELECT c1.companyID, c1.companyName AS text_to_search
                      FROM company c1
                    UNION
                    SELECT c.companyID, c.companyDescription AS text_to_search
                      FROM company c1
                    UNION
                    SELECT c1.companyID, c1.copmanyDrastiriotita AS text_to_search
                      FROM company c1
                   ) AS c3 ( companyID, text_to_search )
 WHERE c2.companyVisible = 1
       AND c3.text_to_search LIKE '%Keyword%'

Upvotes: 0

PravinS
PravinS

Reputation: 2584

Because of OR clause

Apply brackets around AND ...OR clause

Use this

SELECT c.companyID, c.companyDescription , c.companyName ,c.copmanyDrastiriotita, c.companyVisible
FROM company c
WHERE c.companyVisible = 1
AND (c.companyDescription LIKE '%Keyword%'
OR c.companyName LIKE '%Keyword%'
OR c.copmanyDrastiriotita LIKE '%Keyword%')

Upvotes: 1

jarlh
jarlh

Reputation: 44766

AND goes before OR, so you need to add parentheses.

SELECT c.companyID, c.companyDescription , c.companyName ,c.copmanyDrastiriotita, c.companyVisible
FROM company c
WHERE c.companyVisible = 1
AND (c.companyDescription LIKE '%Keyword%'
     OR c.companyName LIKE '%Keyword%'
     OR c.copmanyDrastiriotita LIKE '%Keyword%')

Upvotes: 2

Related Questions