How to treat a field in SQL SERVER

Asked

Viewed 69 times

1

I have two tables in SQL one of EMPLOYEE and another of Dependents and I need to bring the name of all employees, regardless of whether or not they have dependents and for those who have, I need to bring the names of dependents as well. For those who do not have, I have to treat the field with the information "does not have dependents". The problem that so far my query is returning each employee name with all dependents. If anyone can help me I thank you.

SELECT              EMP.NOME            AS  NOME_FUNCIONARIO,
                    DEP.NOME            AS  NOME_DEPENDENTE
FROM                TB_EMPREGADO        AS  EMP
FULL OUTER JOIN     TB_DEPENDENTE       AS  DEP
ON                  EMP.COD_DEPTO       =   COD_DEPTO

Here the image of the two tables

Aqui a imagem das duas tabelas

sql-server query join
  • 1

    As I do not believe that its application allows the registration of dependents who are not linked to an employee a TB_EMPREGADO LEFT OUTER JOIN TB_DEPENDENTE would be more appropriate. As for the message, just use COALESCE(DEP.NOME, 'Não poussui dependentes') AS NOME_DEPENDENTE.

  • 1

    In addition, use the CODFUN columns of the tables at the junction (left Join in the case)

1 answer

1


Try

SELECT EMP.NOME as NOME_FUNCIONARIO,
       coalesce (DEP.NOME, 'não possui dependente') as NOME_DEPENDENTE
  from TB_EMPREGADO as EMP
       left join TB_DEPENDENTE as DEP on EMP.CODFUN = DEP.CODFUN;

If you want dependent names to be in a single line, you can use the STRING_AGG function:

SELECT EMP.NOME as NOME_FUNCIONARIO,
       coalesce (string_agg (DEP.NOME, '; '), 'não possui dependente') as NOME_DEPENDENTE
  from TB_EMPREGADO as EMP
       left join TB_DEPENDENTE as DEP on EMP.CODFUN = DEP.CODFUN
  group by EMP.NOME;

The function STRING_AGG was implemented in the 2017 version of SQL Server. If the version of SQL Server in use is earlier, you will find other solutions in Concatenation of several lines in the same column.

Browser other questions tagged sql-server query join

You are not signed in. Login or sign up in order to post.