Query joining two tables and populating fields conditionally

Navigation
1

I'm a beginner in SQL and would like to know if they could help me with an issue.

I have two tables, one is Cadastro_Func and the other CadastroFocal .

'Cadastro_Func' has the columns:

  • 'Nickname'
  • 'Name'
  • 'Cargo'

'Register_Focal' has the columns:

  • 'Community'
  • 'Nickname'
  • 'PontoFocal'

I would like to select the 'FetchPoint' field of the 'FetchCatalogue' table and replace the 'LastName' field with its name in the 'FetchCatalogue' table in addition to the 'Community'.

Example:

Cadastro_Func :

  • Nickname: Ale
  • Name: Alessandro
  • Position: Manager

Fatal_Count :

  • Community: Logistics
  • Nickname: Ale
  • PontoFocal: Rubens

Result :

  • Community: Logistics
  • Name: Alessandro
  • PontoFocal: Rubens

But the real difficulty is to add to this same selection a qualification per job, where:

  • Names whose 'Position' is 'Manager' are separated in the 'NameGast' column;
  • Names whose 'Title' is 'Coordinator' are separated in the 'ComputerName' column.

It would be something like:

Zip_Func :
Surname - First name - Position
Ale - Alessandro - Manager
Ge - Jorge - Coordinator
Li - Linda - Manager

Register_Focal :
Community - Surname - First name
Logistics - Ale - Rubens
Production - Ge - Luna
P & D - Li - Joseph

Output :
Community - GestetnerName - EngineerName - PontoFocal
Logistica - Alessandro - xxxx - Rubens
Production - xxxx - Jorge - Luna
P & D - Linda - xxxxx - José

Thank you in advance, this is my first question here.

    
asked by anonymous 17.10.2018 / 05:42

1 answer

2

If your SQL Server version is prior to 2012 , you can use the expression CASE to solve your problem: 

SELECT
  fo.Comunidade,
  NomeGestor = CASE fu.Cargo WHEN 'Gestor' THEN fu.Nome ELSE NULL END,
  NomeCoordenador = CASE fu.Cargo WHEN 'Coordenador' THEN fu.Nome ELSE NULL END,
  fo.PontoFocal
FROM
  Cadastro_Func AS fu
  INNER JOIN Cadastro_Focal AS fo ON fo.Apelido = fu.Apelido
ORDER BY
  fo.Comunidade

But if your SQL Server version is greater than or equal to 2012 , you can use the IIF : 

SELECT
  fo.Comunidade,
  IIF(fu.Cargo = 'Gestor', fu.Nome, NULL) AS NomeGestor,
  IIF(fu.Cargo = 'Coordenador', fu.Nome, NULL) AS NomeCoordenador,
  fo.PontoFocal
FROM
  Cadastro_Func AS fu
  INNER JOIN Cadastro_Focal AS fo ON fo.Apelido = fu.Apelido
ORDER BY
  fo.Comunidade
    
17.10.2018 / 07:02
Display first image of a dynamic table in php Compare the value of a column with the checkbox