Mysql Join table based on column name and field value

Question

I wish to update a table where the value of a cell in tableB is a reference to a column name in tableA. It is a bit difficult to explain so below an example of the existing tables and the desired output:

Existing tables:

TableA
Dates       |   f1  |   f2  |   f3
1-1-2017    |   0   |   0   |   0       
2-1-2017    |   0   |   0   |   0       
3-1-2017    |   0   |   0   |   0       
4-1-2017    |   0   |   0   |   0   

TableB
Dates       |   Label   |   Counter 
1-1-2017    |   f1      |   1   
1-1-2017    |   f2      |   2   
2-1-2017    |   f1      |   1   
2-1-2017    |   f2      |   3   
2-1-2017    |   f3      |   2   
3-1-2017    |   f2      |   4   
4-1-2017    |   f3      |   2   
4-1-2017    |   f2      |   1

Desired output

TableA
Dates       |   f1  |   f2  |   f3
1-1-2017    |   1   |   2   |   0       
2-1-2017    |   1   |   3   |   2       
3-1-2017    |   0   |   0   |   4       
4-1-2017    |   0   |   1   |   2

Is something like this possible in mysql?

The title sais cross table update, but you are showing a join in your desired output could you clarify? For me it looks like a view you want to create — WernerW, Nov 15 '17 at 11:00
First, your DB design is not proper. If it's always three attributes in Table A then we can update table A it by a hard-coded query. — Bhushan, Nov 15 '17 at 11:06

WernerW · Accepted Answer · 2017-11-15T11:19:04.633

1

This code will produce a result similar to your output, but it is a result and you cannot update it directly (you update TableB as normal) Also if there are no count for a specific date, no record will show for that date, and you might want to cross join your date table for this.

SELECT A.Dates
,SUM(CASE WHEN Label = 'f1' THEN Counter ELSE 0 END) AS f1
,SUM(CASE WHEN Label = 'f2' THEN Counter ELSE 0 END) AS f2
,SUM(CASE WHEN Label = 'f3' THEN Counter ELSE 0 END) AS f3 
FROM TableA A
LEFT JOIN TableB B ON B.Dates = A.Dates
GROUP BY A.Dates

edited Nov 15 '17 at 11:19

answered Nov 15 '17 at 11:06

WernerW

792
11
27

I want the date range to be sequential. If for example label f3 has not record for 3-1-2017 then no record will be returned. In these situations a record with a 0 value should be returned. – Nebu Nov 15 '17 at 11:13
Do you want to update TableA with the values? Or is it okay to have it in a view/query? I joined TableA as if it was a date table, you should have a look at how some people use date tables for this purpose. – WernerW Nov 15 '17 at 11:20
A view/query is okay. – Nebu Nov 15 '17 at 11:22
Okay, i believe the last update to the code will help you. If you want to see an alternative way to do it you can read up on MySQL Pivot queries here: https://stackoverflow.com/questions/7674786/mysql-pivot-table – WernerW Nov 15 '17 at 11:23

Mysql Join table based on column name and field value

1 Answers1