I have two tables in SQL and I need to perform a search. The tables and fields are:
Employees: 'code' and 'EmployeeName' Team: 'CodeEquipe' and 'RegisteredEngine', where 'RegisteredEngine' is linked to the 'Code' of the Employees table;
The result displayed should be the 'teamcode' of the selected team and 'teamname'.
How do I do this search?
Hello, considering that funcionarioRegistrado is a foreign key of code, there are some ways of doing the query, one of them:
SELECT e.codigoEquipe, f.nomeFuncionario FROM Equipe e INNER JOIN Funcionarios ON e.funcionarioRegistrado = f.codigo;
This query returns only the results where the two tables match.
If you want all team records, even if you do not have employees null , you should change INNER to LEFT .
And if you want all employees, even if they do not have staff, switch INNER to RIGHT .
To do this, you need to use the JOIN of SQL, which is used 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 exuberant, by naming the tables like this:
SELECT e.codigoEquipe, f.nomeFuncionario FROM equipe e INNER JOIN funcionarios f ON e.funcionarioRegistrado = f.codigo;