CODEWITHSUNDEEP
sqlmariadb
Pkchkchiseu
Pkchkchiseu

Reputation: 283

SQL - Selecting from a multiples values field

I'm using Teampass, here is a minimised example of the tables schema

Teampass_Roles_Title :

+----+----------------------+
| id | title                |
+----+----------------------+
|  1 | Title 1              |
|  2 | Title 2              |
|  3 | Title 3              |
|  4 | Title 4              |
|  5 | Title 5              |
|  6 | Title 6              |
  ...
+----+----------------------+

and Teampass_Users

+------------+------------------------------------------------------------+
| login      | fonction_id                                                |
+------------+------------------------------------------------------------+
| AAA        |                                                            |
| BBB        |                                                            |
| CCC        | 12;21;                                                     |
| DDD        | 3;4;5;6;7;8;9;14;15;                                       |
| EEE        | 3;7;                                                       |
| FFF        | 3;7;                                                       |
| GGG        | 3;4;5;6;7;8;9;14;15;21;                                    |
| HHH        | 3;4;5;6;7;8;9;14;15;21;                                    |
  ...
+------------+------------------------------------------------------------+

the numbers in fonction_id are the ids from the table Teampass_Roles_Title Instead of using an intermediate table, they opted for this model, and I dont know if there is any way to have for example a liste of all users that access to Title 6 

+------------+------------------------------------------------------------+
| login      | fonction_id                                                |
+------------+------------------------------------------------------------+
| DDD        | 3;4;5;6;7;8;9;14;15;                                       |
| GGG        | 3;4;5;6;7;8;9;14;15;21;                                    |
| HHH        | 3;4;5;6;7;8;9;14;15;21;                                    |
  ...
+------------+------------------------------------------------------------+

Upvotes: 1

Views: 111

Answers (4)

Rajat
Rajat

Reputation: 5803

You could also just modify the LIKE like this

where function_id like '%;%6%;%' or function_id like '6%;%'

Upvotes: 1

forpas
forpas

Reputation: 164089

With the operator LIKE:

select * from Teampass_Users
where 
  concat(';', fonction_id, ';') 
  like 
  concat('%;', (select id from Teampass_Roles_Title where title = 'Title 6'), ';%')

See the demo.
Results:

| login | fonction_id             |
| ----- | ----------------------- |
| DDD   | 3;4;5;6;7;8;9;14;15;    |
| GGG   | 3;4;5;6;7;8;9;14;15;21; |
| HHH   | 3;4;5;6;7;8;9;14;15;21; |

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

The database model is not great, to say the least. The query that can do what you need is:

select
  u.*
from Teampass_Roles_Title t
join Teampass_Users u on u.fonction_id like concat('', t.id, ';%')
                      or u.fonction_id like concat('%;', t.id, ';%')
where t.title = 'Title 6'

However, this won't be optimal in terms of resource usage. I don't see a way around it, unless you fix the database model.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is a really bad data model. Sometimes, we are stuck with other people's really, really bad decisions. You can do what you want using find_in_set() and replace():

where find_in_set(6, replace(function_id, ';', ',')) > 0

Upvotes: 2

Related Questions