Foreign SQL key

Question

I have two tables in SQL and I need to perform a search. The tables and fields are:

Employees: code and employee name Team: 'codigoEquipe' and 'functionRegistered', where 'functionRegistered' is linked to 'code' of the Workstation table;

The result displayed must be the 'codeEquipe' of the selected team and 'functionName'.

How do I do this research?

Answer 1

Hello, whereas funcionarioRegistrado is foreign key code, there are some ways to make the query, one of them:

SELECT e.codigoEquipe, f.nomeFuncionario FROM Equipe e INNER JOIN Funcionarios ON e.funcionarioRegistrado = f.codigo;

This query will return only the results where there is correspondence in the two tables.

If you want all team records, even if you don’t have employees null, you must change the INNER for LEFT.

And if you want all the staff, even if they don’t have the staff, you switch the INNER for RIGHT.

Answer 2

This requires the use of JOIN SQL, which serves to query data in more than one table at the same time.

In your case it would look like this:

SELECT equipe.codigoEquipe, funcionarios.nomeFuncionario FROM equipe INNER JOIN funcionarios ON equipe.funcionarioRegistrado = funcionarios.codigo;

You can also make an abbreviation of the tables to be more exhilarated, nicknamed the tables like this:

SELECT e.codigoEquipe, f.nomeFuncionario FROM equipe e INNER JOIN funcionarios f ON e.funcionarioRegistrado = f.codigo;