Inner Join with 3 different tables and Joins

Question

Greetings,

I need to make a query in the database that takes into account 3 tables. We use a proprietary application that has no automatic update, so I need to monitor who is not with the latest version and update it.

The query itself, I’ve already solved. However, here are about 200 users and several managers. To make my life easier and not have to go from user to user updating the app, I need to find out who the people manager is and ask the managers to bring in the smatphone so I can update it.

In this section, three tables are taken into account: USER, EMPLOYEE and EMPLOYEE. To fetch active users who are outdated I made the following query.

SELECT USUARIO.NOME,
USUARIO.ID,
USUARIO.TIPO,
USUARIO.AP,
USUARIO.VERSAO,
USUARIO.UNIQUEID,
(SUBSTRING(USUARIO.DTSINCRONIA,7,2)+ '/'+SUBSTRING(USUARIO.DTSINCRONIA,5,2)+'/'+SUBSTRING(USUARIO.DTSINCRONIA,1,4)) AS DATA
FROM USUARIO INNER JOIN FUNCIONARIO ON (USUARIO.ID = FUNCIONARIO.USUARIO)
WHERE USUARIO.ATIVO = 'S'
AND USUARIO.VERSAO NOT LIKE '%224'
AND USUARIO.VERSAO <> ''
AND FUNCIONARIO.DTDEMISSAO = ''
AND FUNCIONARIO.DELETED <> '1'
ORDER BY USUARIO.VERSAO;

To find out who is the Manager of these FUNIONARIOS I need to consult the FUNCIOSUBORDINADO table. However, I don’t have much idea how I’m going to do that. Can anyone help me in how I’m going to do this consultation?

Answer 1

If I understand the problem well, I believe it is enough to include the table in your query:

SELECT u.NOME,
u.ID,
u.TIPO,
u.AP,
u.VERSAO,
u.UNIQUEID,
(SUBSTRING(u.DTSINCRONIA,7,2)+ '/'+SUBSTRING(u.DTSINCRONIA,5,2)+'/'+SUBSTRING(u.DTSINCRONIA,1,4)) AS DATA,
fs.SUBORDINADO
FROM USUARIO u INNER JOIN FUNCIONARIO f ON (u.ID = f.USUARIO)
INNER JOIN FUNCIONARIOSUBORDINADO fs ON (f.CODIGO = fs.CODIGO)
WHERE u.ATIVO = 'S'
AND u.VERSAO NOT LIKE '%224'
AND u.VERSAO <> ''
AND f.DTDEMISSAO = ''
AND f.DELETED <> '1'
ORDER BY u.VERSAO;