SQL Query multiple AND and OR's not working

Question

I have a cell that contains values separated by a double pipe. I am trying to search over the contents of this cell with the following, (where 10 is the number to search for) %10, 10%, %10% and 10

My query only seems to be returning 10. none of the other variations. Can someone please advise me why it's not working?

Many thanks in advance. (the SQL query you see below is what is exported from a prepared PDO query statement)

SELECT  `Hat`.`id` AS  `Hat_id` ,  `Hat`.`hatCode` AS  `Hat_hatCode` ,  `Hat`.`hatCodeOther` AS  `Hat_hatCodeOther` ,  `Hat`.`name` AS  `Hat_name` ,  `Hat`.`description` AS  `Hat_description` , `Hat`.`colorId` AS  `Hat_colorId` ,  `Hat`.`collectionId` AS  `Hat_collectionId` ,  `Hat`.`mainPicture` AS  `Hat_mainPicture` ,  `Hat`.`subPicture` AS  `Hat_subPicture` ,  `Hat`.`type` AS  `Hat_type` , `Hat`.`featured` AS  `Hat_featured` ,  `Hat`.`published` AS  `Hat_published` ,  `Hat`.`deleted` AS  `Hat_deleted` 
FROM  `modx_hats` AS  `Hat` 
WHERE (
(
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '1'
)
AND  `Hat`.`colorId` =  '%||10||%'
OR  `Hat`.`colorId` =  '10||%'
OR  `Hat`.`colorId` =  '%||10'
OR  `Hat`.`colorId` =  '10'
)
LIMIT 0 , 30

WHERE ( ( `Hat`.`published` =1 AND `Hat`.`collectionId` = '1' ) (AND `Hat`.`colorId` = '%||10||%') (OR `Hat`.`colorId` = '10||%' OR `Hat`.`colorId` = '%||10' OR `Hat`.`colorId` = '10') ) — Awlad Liton, Jan 13 '14 at 16:21
You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a name that is a reserved word, or you have embedded spaces or punctuation in them, and those are terrible practices to avoid anyway. — Andy Lester, Jan 13 '14 at 16:23
isn't `%` a wildcard in SQL, I know it is in SQLServer. you would have to find a way to escape the Symbol — Malachi, Jan 13 '14 at 16:26
@AndyLester - not to mention the terrible practise of not normalizing a database properly, so you end up needing to do queries like this that can't take advantage of any indexing — Mark Baker, Jan 13 '14 at 16:26
The question here is relating to why the query isn't working, not best practice. This is what I've got to work with anyway. Working with substandard database designs... Client doesn't want everything redone. — Graeme Leighfield, Jan 13 '14 at 16:38
I take it `%` were being used as wildcards here? I totally read the question wrong. to everyone that voted on the answer, you should also vote on the question. `LIKE` and `=` and the use of the `%` wildcard could be tricky for a new SQL programmer. no one really says *"you can only use wildcards with LIKE statements"* — Malachi, Jan 13 '14 at 16:47

Raul Andres · Accepted Answer · 2014-02-21T07:50:17.690

10

I think that you should use LIKE instead of =:

AND  `Hat`.`colorId` LIKE '%||10||%'
OR  `Hat`.`colorId` LIKE '10||%'
OR  `Hat`.`colorId` LIKE '%||10'
OR  `Hat`.`colorId` =  '10'

edited Feb 21 '14 at 07:50

answered Jan 13 '14 at 16:21

Raul Andres

3,766
15
24

This did it. A fresh set of eyes - brilliant. Thanks ever so much Raul. Will accept this as answer when I'm allowed too. – Graeme Leighfield Jan 13 '14 at 16:23
this won't return values that are written `||10` or `10||` it will only return values that contain `||10||` – Malachi Feb 20 '14 at 16:48
1

@Malachi I assumed that `LIKE` should be used in every line using wildcard. Now I've edited post with the complete line – Raul Andres Feb 21 '14 at 07:53
I discussed this answer with someone on codereview and realized you hadn't posted the entire SQL. Thanks – Malachi Feb 21 '14 at 13:11

score 3 · Answer 2 · answered Jan 13 '14 at 16:23

For wildcart you should use LIKE
Try this:

 SELECT  `Hat`.`id` AS  `Hat_id` ,  `Hat`.`hatCode` AS  `Hat_hatCode` ,  `Hat`.`hatCodeOther` AS  `Hat_hatCodeOther` ,  `Hat`.`name` AS  `Hat_name` ,  `Hat`.`description` AS  `Hat_description` , `Hat`.`colorId` AS  `Hat_colorId` ,  `Hat`.`collectionId` AS  `Hat_collectionId` ,  `Hat`.`mainPicture` AS  `Hat_mainPicture` ,  `Hat`.`subPicture` AS  `Hat_subPicture` ,  `Hat`.`type` AS  `Hat_type` , `Hat`.`featured` AS  `Hat_featured` ,  `Hat`.`published` AS  `Hat_published` ,  `Hat`.`deleted` AS  `Hat_deleted` 
    FROM  `modx_hats` AS  `Hat` 
    WHERE (
    (
    `Hat`.`published` =1
    AND  `Hat`.`collectionId` =  '1'
    )
    AND  `Hat`.`colorId` LIKE  '%||10||%')
    OR  `Hat`.`colorId` LIKE  '10||%'
    OR  `Hat`.`colorId` LIKE '%||10'
    OR  `Hat`.`colorId` LIKE '10'

    )

LIMIT 0 , 30

score 2 · Answer 3 · answered Jan 13 '14 at 16:23

Can you try this, Also, For better results - added () for all OR i.e grouping

WHERE (
(
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '1'
)
AND ( `Hat`.`colorId` LIKE '%||10||%'
....^
OR  `Hat`.`colorId`  LIKE  '10||%'
OR  `Hat`.`colorId` LIKE  '%||10'
OR  `Hat`.`colorId` =  '10'
  )
..^
)
LIMIT 0 , 30

score 2 · Answer 4 · answered Jan 13 '14 at 16:45

Look at this running sql fiddle based on your schema/data/query: http://sqlfiddle.com/#!2/48da8/3

You should use the LIKE operator and put your parenthesis correctly based on the AND and OR priority. Also, cleaning up your query, it looks like this:

SELECT Hat.id           AS Hat_id, 
       Hat.hatcode      AS Hat_hatCode, 
       Hat.hatcodeother AS Hat_hatCodeOther, 
       Hat.name         AS Hat_name, 
       Hat.description  AS Hat_description, 
       Hat.colorid      AS Hat_colorId, 
       Hat.collectionid AS Hat_collectionId, 
       Hat.mainpicture  AS Hat_mainPicture, 
       Hat.subpicture   AS Hat_subPicture, 
       Hat.type         AS Hat_type, 
       Hat.featured     AS Hat_featured, 
       Hat.published    AS Hat_published, 
       Hat.deleted      AS Hat_deleted 
FROM   modx_hats AS Hat 
WHERE  Hat.published = 1 
       AND Hat.collectionid = '1' 
       AND ( Hat.colorid LIKE '%||10||%' 
              OR Hat.colorid LIKE '10||%' 
              OR Hat.colorid LIKE '%||10' 
              OR Hat.colorid LIKE '10' ) 
LIMIT  0, 30

The backquote isn't necessary, as long as you don't use restricted naming characters, like the space character.

score 1 · Answer 5 · answered Jan 13 '14 at 16:27

I believe what you want is this:

SELECT
    id           AS Hat_id,
    hatCode      AS Hat_hatCode,
    hatCodeOther AS Hat_hatCodeOther,
    name         AS Hat_name,
    description  AS Hat_description,
    colorId      AS Hat_colorId,
    collectionId AS Hat_collectionId,
    mainPicture  AS Hat_mainPicture,
    subPicture   AS Hat_subPicture,
    `type`       AS Hat_type,
    featured     AS Hat_featured,
    published    AS Hat_published,
    deleted      AS Hat_deleted
FROM  modx_hats
WHERE
    published = 1
    AND  
    collectionId = '1'
    AND ( 
            colorId =  '%||10||%'
        OR  colorId =  '10||%'
        OR  colorId =  '%||10'
        OR  colorId =  '10'
    )   
LIMIT 0, 30

Note that I removed the backticks from all your table and column definitions except for "type" which is a reserved word.

score 1 · Answer 6 · edited May 23 '17 at 12:21

~~you need to escape the Percentage Symbol~~

the answer to this question should show you how to do it.

You could also create a query like this

SELECT * FROM HatTable
WHERE colorId LIKE '%10%'

this should give you all the options that have 10 in them.

Update

This will give you anything that has a 10 in it. so this is probably not what you are looking for.

Examples of selection using the above query

3210
||10||
||10
10||
10
1032
435262 10 32456234

SQL Query multiple AND and OR's not working

6 Answers6

Linked