Mysql query involving Profession and Note information registered in the same column

Question

Consider a table with the following structure: the column contract code corresponds to a foreign key from another table. It is a contract code. Each contract has basically 4 records in this table in question.

The column inf_valor records the types of information: Profession, Address, Name and Note, and each information is recorded separately in this table. In this way, to know which type the information recorded in inf_valor refers to, there is also the column inf_codigo_variable, which also refers to a foreign key from another table.

Through this column (inf_codigo_variable), it is possible to know what kind of information inf_valor refers to. For example, if inf_passcode=117, inf_value refers to the profession. If inf_passcode=124, inf_value refers to the note.

So, I’m looking to mount a query that meets some conditions at the same time, as follows:

Show how many contracts have Note Great by profession Systems Analyst.

In the following way: (inf_variable code) = 124 and (inf_value)=Great and at the same time (inf_code_variable) = 117 e (inf_value)=Systems Analyst

I put the table on http://sqlfiddle.com/#! 9/895e8c/1

Note that there are only 4 contracts in this table (58,59,60,61). As stated above, each contract has 4 records, each one pointing to a different type of information.

In this case, the query has to return only 1 contract, because only the contract with code 58 that satisfies this condition.

Answer 1

If I understand correctly:

SELECT count(inf_codigo_contrato) FROM sua_tabela
WHERE (inf_codigo_variavel=117 AND inf_valor='ANALISTA DE SISTEMAS') OR (inf_codigo_variavel=124 AND inf_valor='OTIMO');

With your new explanations maybe this query meets:

SELECT count(DISTINCT inf_codigo_contrato) FROM sua_tabela
WHERE EXISTS (SELECT 1 FROM sua_tabela WHERE inf_codigo_variavel=117 AND inf_valor='ANALISTA DE SISTEMAS') AND
      EXISTS (SELECT 1 FROM sua_tabela WHERE inf_codigo_variavel=124 AND inf_valor='OTIMO');

Has really gone incomplete.

SELECT count(DISTINCT inf_codigo_contrato) FROM sua_tabela A
WHERE EXISTS (SELECT 1 FROM sua_tabela B WHERE A.inf_codigo_contrato=B.inf_codigo_contrato AND B.inf_codigo_variavel=117 AND B.inf_valor='ANALISTA DE SISTEMAS') AND
      EXISTS (SELECT 1 FROM sua_tabela C WHERE A.inf_codigo_contrato=C.inf_codigo_contrato AND C.inf_codigo_variavel=124 AND C.inf_valor='OTIMO');

Answer 2

Can try this solution, I believe you need two selects to do this, one for each condition.

    SELECT count(t.inf_codigo_contrato) FROM tabela AS t
     WHERE t.inf_codigo_variavel=117 
       AND t.inf_valor='ANALISTA DE SISTEMAS'
       AND t.inf_codigo_contrato IN (
           SELECT inf_codigo_contrato 
             FROM tabela
            WHERE  inf_codigo_variavel=124 
              AND inf_valor='OTIMO'
)

Answer 3

You can do this in several ways, one of them is by creating two subtables, one that obeys the 1st condition and the other that obeys the 2nd condition. as in the example below.

select count(otimo.inf_codigo_contrato) from 
(select inf_codigo_contrato from tabela where inf_codigo_variavel=117 and inf_valor="Analista de Sistemas") as anSistema 
inner join 
(select inf_codigo_contrato from tabela where inf_codigo_variavel=124 and inf_valor="Ótimo") as otimo 
on anSistema.inf_codigo_contrato=otimo.inf_codigo_contrato

Another option would be to use functions, there you will choose the best for your case, see performance issue and everything else.

Answer 4

Follow-up:

 SELECT count(*) as qtd_otimo
            FROM tabela t1
                INNER JOIN tabela t2 ON t2.inf_codigo_contrato = t1.inf_codigo_contrato
            WHERE
                t1.inf_codigo_variavel = 117
                AND t1.inf_valor='ANALISTA DE SISTEMAS'
                AND t2.inf_codigo_variavel = 124
                AND t2.inf_valor = 'Ótimo'
            GROUP BY t1.inf_codigo_contrato