Selecting SQL Related Records

Question

Selecting SQL Related Records

Navigation

#1 by (1 votes)

0

I have two tables, one that calls grupo where the groups created for access to an application are stored. For example, if you have the CADASTRO_ADMIN group, you will have full permission; now, if you have the group CADASTRO_USR you will only have user permission.

The other table is named grupo usuário , where the user's CPF is stored along with its permission group, see:

GROUP

USERGROUP

The select I need to do is: Select the groups that the user admins along with the groups that have the administrator group code in the cod_grupo_admin column.

For example, user 222.222.222-11 is administrator of group 18 , so my query needs to return the group 18 , 22 and 30 , as it has cod_grupo_admin = 18 .

I tried this query, but the problem is that it only returns me the code of the admin group, not the others.

SELECT g.* FROM grupo g
LEFT JOIN grupo_usuario u ON (g.codigo = u.codigo_grupo)
WHERE
g.administrador = true AND u.cpf='222.222.222-11'

sql postgresql

asked by anonymous 21.07.2017 / 01:03

1 answer

How to specify object in getcomponent? Write AJAX Data

score 1 · Accepted Answer

I have not tested, but I believe that's what you're looking for:

SELECT grupo.codigo
  FROM grupo
 INNER JOIN grupo_usuario ON (grupo.codigo = grupo_usuario.codigo_grupo)
 WHERE grupo_usuario.cpf = '...'
   AND grupo.administrador = true
UNION
SELECT grupo.codigo
  FROM grupo
 INNER JOIN grupo_usuario ON (grupo.cod_grupo_admin = grupo_usuario.codigo_grupo)
 WHERE grupo_usuario.cpf = '...'

Explaining a little:

The first select takes the group where the user is directly administrator.

The second takes the groups that have the user group as the admin group.