Mysql – Comparing strings in sql

MySQL

I have a list of string values for example ('a', 'b', 'c', 'd', 'e'). Now I have a database lets say (T1) having two columns for example (user and permission). Every user has some permissions. For example.

|user   | permission|
| user1 | a         |
| user2 | a,b,f     |
| user3 | b,c       |
| user4 | e,d       |
| user5 | f,g       |

I want to write a query which can retrieve the users who are using those permissions. What I tried until now is below.

select * from T1 where permissions IN ('a', 'b', 'c', 'd', 'e')

After running this I only get user1 nothing else. But the answer should be user2 and others also because they are using permission a also.

Note: This is just an example. The strings I have are more than 25 and the database has more than 100 values.

Best Answer

What you can do is something like this (see the fiddle here):

Table:

CREATE TABLE t
(
  user VARCHAR (10) NOT NULL,
  permission VARCHAR (10) NOT NULL
);

Data:

INSERT INTO t VALUES
('user1', 'a'),
('user2', 'a, b, f'),
('user3', 'b, c'),
('user4', 'e, f'),
('user5', 'f, g');

Query:

SELECT * FROM t
WHERE permission LIKE '%a%'
OR    permission LIKE '%b%'
OR    permission LIKE '%c%'
OR    permission LIKE '%d%'
OR    permission LIKE '%e%';

Result:

user    permission
user1   a
user2   a, b, f
user3   b, c
user4   e, f

user5 is excluded because his permissions are 'f,g' and these don't appear in the sequence a-e.

p.s. welcome to the forum!

Related Solutions

Mysql – Human relations between users

You may want to recognize directional relationships (e.g. "is the mother of", "is the son of") or you may need to acknowledge that relationships are not bilateral insofar as the users don't grant each other exactly the same rights. In that case you need to keep two records in users_relations for each relationship. Otherwise, I would just capture each user linkage once.

Union queries take about twice as long as single queries, but when these queries are built on fast operations like index seeks this isn't so bad. In any case you'd be trading this off against twice as much storage, which mitigates the impact slightly.

I'd be much more concerned about the potential headaches of managing the redundant data (if it is indeed redundant).

You could pick a convention like lowest id_user first to make certain kinds of operations more predictable - such as updates and deletes.

MySQL: select a row if that row is equal for every user

If there are 5 rows per month (1 for every user), this will do:

SELECT m.year, m.month 
FROM month AS m
GROUP BY m.year, m.month
HAVING SUM(finished) = 5 ;

Best Answer

Related Solutions

Mysql – Human relations between users

MySQL: select a row if that row is equal for every user

Related Question