Mysql query, filter

Question

Mysql query, filter

Navigation

#1 by (1 votes)

1

I have a table with People and roles, and I want to select people who do not have the Head of Department role in a selection.

The problem is that if you have the role, the user also shows it and I want it to be shown at the moment you have the role of boss.

Select Id_usuario 
from union_pers_rols
where Id_rol != 'Jefe Departamento'

Table union_pers_rols 2 columns: User_id (Is a name) Id_rol, Has the (role) of that person

I have to remove all the people who do not have the "Department Head" role. The problem is that there are users with more than 1 role in that "User" table Then I get the name of that person because he is taking it by the role User

Before the select: User_id Id_rol

edu@
Usuario 

jaime@
Jefe Departamento  

jaime@
Root 

jaime@
Usuario

nose@
Usuario 

pass@
Usuario

After the selection

Id_usuario
edu@
jaime@
jaime@
nose@
pass@

As you can see, he takes me out to Jaime @ because he also has other roles = (

mysql sql

asked by EduBw 03.12.2017 в 20:17

source

1 answer

fragment on android Problem when running Code :: blocks does not start!

score 1 · Accepted Answer

I see it weird that you want to get a list of users using a table where records for users can appear several times according to their rols. Do not you have a separate table usuarios too?

Either way, you need a NOT EXISTS clause:

select distinct r.Id_usuario
  from union_pers_rols r
 where not exists (select null
                     from union_pers_rols j
                    where j.Id_usuario = r.Id_usuario
                      and j.Id_rol = 'Jefe Departamento')

But if you have a dedicated table for the users, then the most logical query would be something like:

select *
  from usuarios u
 where not exists (select null
                     from union_pers_rols r
                    where r.Id_usuario = u.Id_usuario
                      and r.Id_rol = 'Jefe Departamento')