Script to determine a desired column

Asked

Viewed 194 times

2

Good morning! I’m trying to make a script where doing 2 joins, it shows me 3 columns. I mean, what I want is to get the email do lider. However it does not have a specific column, it is inserted your email in the column Email Lider, and there are also the emails do gestores.

Should appear this way:

ID | Email Gestor | Email Lider
-------------------------------
13 | [email protected]  | [email protected]
14 | [email protected]  | [email protected]
15 | [email protected]  | [email protected]
16 | [email protected]  | [email protected]
17 | [email protected]  | [email protected]
18 | [email protected]  | [email protected]

The problem is I’m not finding the right code for it. And so the result I get is this: 

ID | Email Gestor | Email Lider
-------------------------------
13 | [email protected]  | [email protected]
14 | [email protected]  | [email protected]
15 | [email protected]  | [email protected]
16 | [email protected]  | [email protected]
17 | [email protected]  | [email protected]
18 | [email protected]  | [email protected]

That is, instead of getting the mails from leaders, I get emails from managers.

The table to find the name of the leaders is basically like this:

-- Tabelas que estou a fazer consulta:
-- Tabela Tecnicos S --

    IDGestor | NomeGestor | Email
    ----------------------------------------
    13       | pd         | [email protected]    |
    14       | pf         | [email protected]    |
    15       | fp         | [email protected]    |
    16       | fd         | [email protected]    |
    17       | ff         | [email protected]    |
    18       | gg         | [email protected]    |
    19       | lider      | [email protected] |
    20       | lider2     | [email protected] |

-- Tabela Reclamaçoes R --

IDGestor   | Resolvido |
-----------------------
13         |     0     | 
14         |     0     | 
15         |     0     | 
16         |     0     | 
17         |     0     | 
18         |     0     | 
19         |     0     | 
20         |     0     | 

-- Tabela Vendedor S --


        IDGestor   | NomeGestor |
        ------------------------
        13         |     pd     | 
        14         |     pf     | 
        15         |     fp     | 
        16         |     fd     | 
        17         |     ff     | 
        18         |     gg     | 
        19         |    lider   | 
        20         |   lider2   |

That is, the columns link to each other: Complaints with Seller: Manager Id Technical Complaints : Manager Id Seller with Technique : Manager Id and Manager Name

The code I have is this:

SELECT R.ID
    ,VS.Email AS EmailGestor
    ,TS.email AS EmailLider
FROM dbo.Reclamacoes R
INNER JOIN dbo.TECNICOS_S TS ON R.IdGestor = TS.IdTecnico
INNER JOIN dbo.VENDEDORES_S VS ON R.IdGestor = VS.IdVendedor
WHERE R.Resolvido = 0
ORDER BY R.ID

Can anyone help me? Any doubt to understand what I want to know, don’t hesitate to ask. Thanks in advance.

sql sql-server join
  • 3

    How do you know the difference between manager and leader? Edit your question and put the table structure you are using in the query.

  • @Joãomartins has already been changed, can you understand that? The difference between the manager is that the manager has the name "pf" for example, only 2 letters. the leader even has the name "lider" for example.

  • If you’re connecting the table Reclamacoes by the column IdGestor at the table TECNICOS_S by the column IdTecnico (that in the structure that placed I imagine that corresponds to IDGestor) then you won’t be able to get the leader’s email, because you’re associating with the ID, which always brings you the email associated with that ID, and not any other associated with a leader.

  • @Joãomartins then how do I get this email?

  • Then you have to create a column that associates the manager to a leader.

  • create a column or select? the database is from the company where I am interning, I cannot and do not have permissions to create columns in the tables.

  • With this structure I see no way to get the result you want :/

  • @Joãomartins what can I do for you to understand and help me? I really need to finish this as soon as possible

  • Let’s go continue this discussion in chat.

Show 4 more comments

1 answer

1

I tried to simulate here and I saw this missing information! I am posting the query here and you tell me what is missing thank you.

   declare @Tecnicos table(IDGestor int, NomeGestor varchar(10), Email varchar(20))
insert into @Tecnicos values (13,'pd','[email protected]')
insert into @Tecnicos values (14,'pf','[email protected]')
insert into @Tecnicos values (15,'fp','[email protected]')
insert into @Tecnicos values (16,'fd','[email protected]')
insert into @Tecnicos values (17,'ff','[email protected]')
insert into @Tecnicos values (18,'gg','[email protected]')
insert into @Tecnicos values (19,'lider','[email protected]')
insert into @Tecnicos values (20,'lider2','[email protected]')

declare @Reclamacoes table(IDGestor int, Resolvido bit)
insert into @Reclamacoes values (13,0)
insert into @Reclamacoes values (14,0)
insert into @Reclamacoes values (15,0)
insert into @Reclamacoes values (16,0)
insert into @Reclamacoes values (17,0)
insert into @Reclamacoes values (18,0)
insert into @Reclamacoes values (19,0)
insert into @Reclamacoes values (12,0)

declare @Vendedor table(IDGestor int, NomeGestor varchar(20))
insert into @Vendedor values (13,'pd')
insert into @Vendedor values (14,'pf')
insert into @Vendedor values (15,'fp')
insert into @Vendedor values (16,'fd')
insert into @Vendedor values (17,'ff')
insert into @Vendedor values (18,'gg')
insert into @Vendedor values (19,'lider')
insert into @Vendedor values (20,'lider2')

SELECT r.IDGestor , vs.NomeGestor AS EmailGestor ,ts.Email AS EmailLider
FROM @Reclamacoes R
INNER JOIN @Tecnicos TS ON R.IdGestor = TS.IDGestor
INNER JOIN @Vendedor VS ON R.IdGestor = VS.IDGestor
WHERE R.Resolvido = 0
ORDER BY r.IDGestor

Browser other questions tagged sql sql-server join

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