like and not like in one mysql query

Question

I want to do a query containing 'like' and 'not like'.

Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'.

Current query:

'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'.

But that doesn't work. Any tips? thx

Answer 1

Just add "and category"...

SELECT * FROM links 
WHERE category LIKE '1|%' 
  AND category NOT LIKE '1|6|199|%','1|6|200|%' 
ORDER BY score DESC LIMIT 9

Actually, the comma separated condition is not a syntax I'm familiar with. If that's not working, try this instead:

SELECT * FROM links 
WHERE category LIKE '1|%' 
  AND category NOT LIKE '1|6|199|%'
  AND category NOT LIKE '1|6|200|%' 
ORDER BY score DESC LIMIT 9

Answer 2

You can use regexps:

SELECT  *
FROM    links 
WHERE   category LIKE '1|%' 
        AND category NOT REGEXP '^1\\|6\\|(199|200)\\|'
ORDER BY
        score DESC
LIMIT 9

Note that REGEXP's don't use indexes, while LIKE does.

In this query, LIKE '1|%' will serve as a coarse filter using the index on category if any, while REGEXP's will fine filter the results.

Answer 3

i think a bigger problem is that you have de-normalized tables. the correct answer would be to normalize your tables.

but if you can't do that, you should change your tables to comma delimited and use find_in_set() instead:

WHERE FIND_IN_SET('1', category) > 1
  AND FIND_IN_SET('6', category) > 1
  AND FIND_IN_SET('199', category) = 0
  AND find_IN_SET('200', category) = 0