0

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

devlin carnate
  • 8,309
  • 7
  • 48
  • 82
tito
  • 149
  • 3
  • 10
  • separate your concerns!! First, alter table to add the required column; second step is to write a JOIN to expose the filed required and UPDATE the master table – techspider Aug 16 '16 at 20:15
  • Possible duplicate of [How to do 3 table JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query) – devlin carnate Aug 16 '16 at 20:17
  • @techspider: I would like to avoid having to alter table. I am just interested in the result set, coming from a join or a stored procedure or something else. I am not sure if it is possible to add that new column on the fly. – tito Aug 16 '16 at 20:23
  • `Max_Total column needs to be somehow created and populated` my answer was depending on your statement :) if you are simply concerned about result set, you can use SELECT – techspider Aug 16 '16 at 20:34

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wow. That worked. As simple as that. I have been struggling for a few hours with this. Anyway thanks a lot. – tito Aug 16 '16 at 20:37
1

You need to separate your tasks.

  1. Alter your Table1 to add the column Max_Total
  2. Write UPDATE query to update your Max_Total in your Table1.

Query:

UPDATE t1.Max_Total = t2.Max_Total
SET t1.
FROM Table1 t1 
JOIN Table2 t2 ON t1.Day = t2.Day AND t1.EmployeeId = t2.EmployeeId

If you are only concerned about getting a combined result set

SELECT t1.Day, t1.EmployeeId, t1.Total, t1.EmployeeName, t2.Max_Total
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t1.Day = t2.Day AND t1.EmployeeId = t2.EmployeeId

For more information on LEFT JOIN, you can study this tutorial.

CL.
  • 173,858
  • 17
  • 217
  • 259
techspider
  • 3,370
  • 13
  • 37
  • 61
  • Thank you for your help as well. Gordon was a little bit quicker with his answer. Your second statement is exactly what I needed. – tito Aug 16 '16 at 21:22