Mysql doubt compare multiple columns

Question

Goodnight,

I’m with a project where I need to compare which employee meets certain requirements, the structure of the bank is like this:

I have a table with the score requested for each position and in each requirement, I wanted to know if there is a way I list only employees who are within this standard, in the case of the structure image, only José meets the requirements, I wanted something to list the ones that serve the ones that least answer, I think in PHP I can do this manipulation, I wanted to know if there is a way to do this Query right in the bank. Thank you!

Answer 1

tl;dr

This is a case that is solved well with JOIN, after all all all tables have a very regular structure, and a very direct relationship:

SELECT    nome,
          COUNT(*) AS atendidas,
          SUM(pontuacao.valor - padraocargo.valor) AS excedente

FROM      funcionario
          JOIN pontuacao ON codigo = codfunc
          JOIN padraocargo USING(requisito)

WHERE     pontuacao.valor >= padraocargo.valor

GROUP BY  codigo

ORDER BY  COUNT(*) DESC,
          SUM(pontuacao.valor - padraocargo.valor) DESC
;

Exit:

nome  atendidas  excedente
JOSÉ          3         42
JOÃO          2         55

See working on SQL Fiddle.

(at the end of the reply has a version that accounts for the surplus otherwise)

Understanding:

First of all, we have to relate the tables:

JOIN pontuacao ON codigo = codfunc
JOIN padraocargo USING(requisito)

In the case of pontuacao, we want the specific score of each employee, and the fields have different names, so we use ON. In the case of requisito, as the field is equal both in scoring and in padraocargo, we can choose to USING().

Until then, see our JOIN working: (fiddle)

nome  pontuacao  desejado
JOSÉ         67        40
JOSÉ         56        48
JOSÉ         76        69
JOÃO         56        40
JOÃO         87        48
JOÃO         34        69

As we look for cases where the score exceeds the standard, we use this condition:

WHERE pontuacao.valor >= padraocargo.valor

Resulting in:

nome  pontuacao  desejado
JOSÉ         67        40
JOSÉ         56        48
JOSÉ         76        69
JOÃO         56        40
JOÃO         87        48

But we don’t want to compare line by line. We want to know who is more qualified only, so we do one grouping per employee:

GROUP BY  codigo

The field codigo is the employee ID, so we use it as a criterion. This will hide the lines, so we need to return in another way:

SELECT    nome,
          COUNT(*) AS atendidas

And to help in reading, we sort starting with the most punctuated:

ORDER BY  COUNT(*) DESC

Okay, we have resolved what was asked in the question. But there may happen to be a "draw" in the sense that more than one of the employees meet the requirement. To improve the ranking, we can return the excess points in this way:

SUM(pontuacao.valor - padraocargo.valor) AS excedente

And, of course, order as a second criterion:

ORDER BY  COUNT(*) DESC,
          SUM(pontuacao.valor - padraocargo.valor) DESC

Note that the surplus is the one he passed. It has an interesting output if you want to know the general surplus. I won’t go into too much detail not to confuse, but in short it’s just:

• Take out the condition WHERE
• Barter COUNT(*) AS atendidas por SUM(IF(pontuacao.valor >= padraocargo.valor,1,0)) AS atendidas

In this way, the surplus will account for what was not met also. (fiddle)

If you want to return only employees who pass all criteria, you can do so:

• Take away the ordination by atendidas, since only the complete
• Add a HAVING COUNT(*) = 3

Okay, but what if each cargo has different quantities of requirements?

• Do a query first to get the value:

  SELECT @numreq = COUNT(*) FROM padraocargo WHERE cargo = (id do cargo);
  

• Add a HAVING COUNT(*) = @numreq at the end of the second query

Further reading:

What is the difference between INNER JOIN and OUTER JOIN?

Answer 2

Here is a solution that addresses your problem. The logic was simple:

1- Return employees who have not hit the target on one or more requirements.

select f.nome, c.requisito, c.valor VALOR_NECESSARIO, p.valor VALOR_ALCANCADO
  from funcionario f inner join cargo c ON f.cargo = c.cod_cargo
  INNER JOIN pontuacao p ON c.requisito = p.requisito AND c.cod_cargo = f.cargo
  WHERE p.cod_funcionario = f.cod_funcionario
                       and p.requisito = c.requisito
                       and p.valor < c.valor ;

Return :

+------+-----------+------------------+-----------------+
| nome | requisito | VALOR_NECESSARIO | VALOR_ALCANCADO |
+------+-----------+------------------+-----------------+
| JOAO |         2 |               48 |              34 |
+------+-----------+------------------+-----------------+

That is, John did not reach the necessary value in requirement 2.

2- Now, I make a new query that brings all employees with the exception of those who returned in the previous query (ie, will only come those who have hit all targets).

  select f.cod_funcionario COD_FUNC, f.nome NOME, p.valor VALOR_ALCANCADO
    from funcionario f INNER JOIN pontuacao p ON p.cod_funcionario = f.cod_funcionario
   where f.cod_funcionario not in (
select  f.cod_funcionario
  from funcionario f inner join cargo c ON f.cargo = c.cod_cargo
  INNER JOIN pontuacao p ON c.requisito = p.requisito AND c.cod_cargo = f.cargo
  WHERE p.cod_funcionario = f.cod_funcionario
                       and p.requisito = c.requisito
                       and p.valor < c.valor);

Return:

+----------+------+-----------------+
| COD_FUNC | NOME | VALOR_ALCANCADO |
+----------+------+-----------------+
|        1 | JOSE |              67 |
|        1 | JOSE |              56 |
|        1 | JOSE |              76 |
+----------+------+-----------------+