tito tito - 3 months ago 6
MySQL Question

Sql join, extract value from the second table and add it to the first one

I have 2 tables in Mysql. I need to join them somehow to get one value from second table into the first one.

TABLE 1

Day EmployeeId Total EmployeeName

1 2 20 Josh
1 1 20 Mike
2 2 5 Josh
2 1 10 Mike
3 3 5 Eric


TABLE 2

Day EmployeeId Max_Total

1 2 40
1 1 40
2 2 5
2 1 15


I need to get something like TABLE 3

Day EmployeeId Total EmployeeName Max_Total

1 2 20 Josh 40
1 1 20 Mike 40
2 2 5 Josh 5
2 1 10 Mike 15
3 3 5 Eric null


So this Max_Total column needs to be somehow created and populated.
This Day_EmployedId combination is unique in both tables and that should be used somehow to extract values from 2nd table and add it to the first one.

Sometimes first table can have more values, sometimes the second one, but the first one will always be the one that needs to be manipulated/added to.

Any hint will be appreciated. Thanks

Answer

You are looking for a left join on two fields:

select t1.*, t2.max_total
from table1 t1 left join
     table2 t2
     on t1.day = t2.day and t1.employeeid = t2.employeeid;

I would not recommend actually updating table1. You can generate the data as you need it. However, in order for an update to work, you need to add a column to the table first, and then update it.