Doubt about SQL

Question

Good afternoon guys, I am learning SQL and I have to do an exercise that is: Select the first and last names of employees and consultants working in the "Financial" department.

Turns out I’m trying to use this query

SELECT nmemp, snemp, nmconsult FROM emp, consult 
WHERE consult.coddepto = emp.coddepto AND
emp.coddepto in(Select coddepto from depto where nmdepto = 'financeiro')

I tried to use John too but it didn’t help

SELECT nmemp, snemp,nmconsult FROM emp 
LEFT JOIN consult ON consult.coddepto = emp.coddepto 
LEFT JOIN depto ON  nmdepto = 'financeiro' AND consult.coddepto = depto.coddepto 

The result is going wrong as in the image below, I would like to know what I am missing, because I can’t find my mistake

Below follows the images with the table names that need to be used

Table name = Consult https://i.stack.Imgur.com/rNnI4.png

Table name = depto https://i.stack.Imgur.com/8tna7.png

Table name = emp https://i.stack.Imgur.com/oCnf6.png

Answer 1

To solve this problem there are two alternatives. The first would be to make the queries separately and use UNION ALL to join the results of the two tables. The query would look like this:

SELECT nmconsult as nome, snconsult as sobrenome from consult where coddepto in (SELECT coddepto from depto where nmdepto = 'financeiro')
UNION ALL 
SELECT nmemp as nome, snemp as sobrenome from emp where coddepto in (SELECT coddepto from depto where nmdepto = 'financeiro')

UNION ALL serves for these cases where you want to join the result of queries that has the same amount of columns and the same order and type of data in each column.

Another alternative would be, also using UNION ALL, to join the results of the queries and apply the filter per department at the end. The consultation would look like this:

SELECT * from (
SELECT nmconsult as nome, snconsult as sobrenome, coddepto from consult
UNION ALL 
SELECT nmemp as nome, snemp as sobrenome, coddepto from emp
) as tabela where coddepto in (SELECT coddepto from depto where nmdepto = 'financeiro')

In this example, the nickname (alias) "table" was assigned to the results of the two queries. In this way, filters can be applied to the results.