ForeverSJC ForeverSJC - 13 days ago 6
MySQL Question

Query using % with IN clause

I've been looking for this answer here but could not find any, the question is this.

This is the table

USER | PERMISSION
PEDRO | ALL
BARBARA | READ - WRITE
FULANO | WRITE


Its a exemple table, what I want is to query all users that have ALL in permission and any user that has the permission I want for that page, like READ or WRITE using IN

I'm using this:

select * from senha where permission IN ('read', 'all')


Its only querying the "pedro" because it has all, and not "barbara" because it has more words insted of just READ, tried this too but no success

select * from senha where permission IN ('%read%', 'all')

Answer

You cannot achieve LIKE like behavior using IN. Use OR to combine the two clauses:

SELECT * FROM senha 
WHERE (permission = 'ALL' OR permission LIKE '%READ%')

As for your revised question, the query would be:

SELECT * FROM senha
WHERE user  = 'PEDRO' AND 
      senha = 'TEST'  AND 
      (permission = 'ALL' OR permission LIKE '%READ%')

Having said that, I would rather normalize the permissions, like this for example:

USER    | PERMISSION
--------+-----------
PEDRO   | ALL
BARBARA | READ
BARBARA | WRITE
FULANO  | WRITE