Join columns from different tables in SQL Server

Question

I am trying to select columns from different tables and create a new table, for example "test". if the inputs from the "Operador" and "Data" column inserted in the tables: "Busy", "Login" can't locate the inputs of the same columns from the "Atendidas" table then it displays NULL. It is probably simple but i can't really get there.

What i want is the following example:

Table Atendidas:

ID   Ano         Mes             Data        Operador   Atendidas
1    2020    JANEIRO 2020     2020/01/01     CCTLT413        30
2    2020    JANEIRO 2020     2020/01/01     CCVTH2498       31
3    2020    JANEIRO 2020     2020/01/01     XKELB80         27
4    2020    JANEIRO 2020     2020/01/01     XKELI28         29
5    2020    JANEIRO 2020     2020/01/01     XKELN94         24

Table Busy:

ID  Ano        Mes             Data     Operador    Busy
1   2020    JANEIRO 2020    2020/01/01  CCTLT413    6812
2   2020    JANEIRO 2020    2020/01/01  CCTL200     3245
3   2020    JANEIRO 2020    2020/01/01  XKELB80     12305
4   2020    JANEIRO 2020    2020/01/01  XKELI28     7764
5   2020    JANEIRO 2020    2020/01/01  XKELN94     6014

Table Login:

ID   Ano       Mes             Data     Operador    Login
1   2020    JANEIRO 2020    2020/01/01  CCTLT413    19475
2   2020    JANEIRO 2020    2020/01/01  CCVTH2498   20209
3   2020    JANEIRO 2020    2020/01/01  XKELB80     21625
4   2020    JANEIRO 2020    2020/01/01  XKELI28     21175
5   2020    JANEIRO 2020    2020/01/01  XKV220      22121

test:

ID     Data         Operador     Atendidas     Busy      Login
1   2020/01/01      CCTLT413        30         6812      19475
2   2020/01/01      CCVTH2498       31         *NULL*    20209
3   2020/01/01      XKELB80         27         12305     21625
4   2020/01/01      XKELI28         29         7764      21175
5   2020/01/01      XKELN94         24         6014      *NULL*

Solution:

This worked, i have to Left join the data which i was not doing it.:

SELECT A.Data, A.Operador, A.Atendidas, B.Busy, B.Login
  from Atendidas as A
       left join [Busy] as B on B.Operador = A.Operador
                                and B.Data = A.Data
       left join [Login] as L on L.Operador = A.Operador
                                 and L.Data = A.Data;

What is your expected output and what you have tried so far include that part also in your question by editing the question? Mention why that not work also? — Suraj Kumar
– Suraj Kumar, Commented Mar 16, 2020 at 10:01
I don't think I get the question. What are you trying to achieve here? — Amit Kumar
– Amit Kumar, Commented Mar 16, 2020 at 10:02
Hello i just edited the post. I am not really sure how to do this, i tried left joins but the results were much different from what i tried in the query above. — rafamaniac
– rafamaniac, Commented Mar 16, 2020 at 10:40

Kamal · Accepted Answer · 2020-03-16 13:55:03Z

1

Joining the tables using a Union should correlate the data from which you can select from. This is assuming no duplicates in each of the table, which can then be managed using SUMs and Group Bys.

SELECT * FROM 
(
SELECT Id, Data, Operador, Atendidas, NULL Busy, NULL Login FROM Atendidas
UNION
SELECT Id, Data, Operador, NULL Atendidas, Busy, NULL Login FROM Busy
UNION
SELECT Id, Data, Operador, NULL Atendidas, NULL Busy,  Login FROM Login
)

You can also try the following, this is assuming the Ids are the same, but you can also join on the Operador column. Lots of assumptions in the data.

SELECT a.Id, a.Data, a.Operador, a.Atendidas, b.Busy, l.Login
FROM Atendidas a
INNER JOIN Busy b ON a.Id = b.Id
INNER JOIN Login l ON a.Id = l.Id

answered Mar 16, 2020 at 13:55

Kamal

2,5222 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rafamaniac Over a year ago

the problem is that the id's are not always the same for a certain operador as explained in my post. i really wanted to get this right but i can't get there.

Kamal Over a year ago

Is operador the same - join on that instead - a.Operador = b.Operador and a.Operador = l.Operador

rafamaniac Over a year ago

Thank you Kamal for your help but i got there somehow, i forget to join the data see my solution above

Collectives™ on Stack Overflow

Join columns from different tables in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related