Query joining two tables and filling in fields conditionally

Asked

Viewed 240 times

1

I’m a beginner in SQL and I was wondering if you could help me with a question.

I have two tables, one is called Cadastro_func and the other Cadastre.

The 'Func_register' has the columns:

  • 'Nickname'
  • 'Name'
  • 'Office'

'Focal Register' has columns:

  • 'Commune'
  • 'Nickname'
  • 'Pointed'

I would like to select the field 'Pontofocal' of the table 'Focal Register' and replace the field 'Nickname' by the respective table name 'Func Register', in addition to 'Community'.

Example:

Cadastro_func:

  • Nickname: Ale
  • Name: Alessandro
  • Office: Manager

Focal register:

  • Commune: Logistic
  • Nickname: Ale
  • Pointed: Rubens

Upshot:

  • Commune: Logistic
  • Name: Alessandro
  • Pointed: Rubens

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

  • Names whose Position is "Manager" are separated in the column 'Manager name';
  • Names whose 'Position' is "Coordinator" are separated in the column 'Coordinator name'.

It would be something like:

Cadastro_func:
Surname - First name - Title
Ale - Alessandro - Manager
Ge - Jorge - Coordinator
Li - Linda - Manager

Focal register:
Community - Surname - First name
Logistica - Ale - Rubens
Production - Ge - Luna
R&D - Li - José

Exit:
Community - Nomegestor - Nomecoordenador - Pontofocal
Logistica - Alessandro - xxxx - Rubens
Production - xxxx - Jorge - Luna
R&D - Linda - xxxxx - José

Thanks in advance, it’s my first question here.

sql sql-server query select

1 answer

3

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 function 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

Browser other questions tagged sql sql-server query select

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