Mount SQL with options

Question

I am using Postgresql in an extension system and I have here two tables. One that stores the extensions and another that stores the permission to link these extensions.

This second table is structured like this:

In the system I set up a filter to bring the extensions according to the filter applied by the user. In case the query below is not returning me the expected result:

SELECT p.ramal FROM servico.ramal_permissao_ramal p WHERE  p.cod_opcao_permissao_ramal = 'RM' AND p.cod_opcao_permissao_ramal = 'LC'

I would need it to return me the extension 1426 but does not return anything. I was using IN but the problem is that IN does not apply a unique filter.

How can I optimize this query for when the user marks RM and LC she bring me only extensions with these permissions?

Thank you!

Answer 1

You are listing two records from this table. One of them as the RM and the other with the LC. The standard way to relate records is with the JOIN. Soon:

SELECT p.ramal
FROM servico.ramal_permissao_ramal p
INNER JOIN servico.ramal_permissao_ramal q ON p.ramal = q.ramal
WHERE p.cod_opcao_permissao_ramal = 'RM'
AND q.cod_opcao_permissao_ramal = 'LC'