Selection of SQL related records

Question

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

The other table is called grupo usuário, where the user’s CPF is stored together with their permission group, see:

GROUP

USER GROUP

The select that need to be done is the following: Select the groups that the user is administrator along with the groups that have the administrator group code in the column cod_grupo_admin.

For example, the user 222.222.222-11 is the group administrator 18, then my query needs to return to the group 18, 22 and 30, because you have the cod_grupo_admin = 18.

I tried this query, but the problem that it only returns me the group code admin, 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'

Answer 1

I haven’t tested it, 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 whose administrator group is the user group.