MySQL: Join table fields to related result set

Question

I have two tables.

The 'parameters' table (below) keeps track of parameters attached to various components:

id (pk) | name | component

The 'parameterLog' table tracks the values of a parameter across time:

action_date | parameter (fk) | value | notes

I have the following result set:

SELECT * FROM parameters WHERE component = 1;
+----+------+-----------+
| id | name | component |
+----+------+-----------+
|  1 | par1 |         1 |
|  2 | par2 |         1 |
+----+------+-----------+

For each entry in the above result set, I need the corresponding and most recent action_date, value, and note from the 'parameterLog' table.

[Edit] For clarity, I'm including the tables with data below.

parameters:
+----+------+-----------+
| id | name | component |
+----+------+-----------+
|  1 | par1 |         1 |
|  2 | par2 |         1 |
|  3 | par1 |         2 |
|  4 | par2 |         2 |
|  5 | par1 |         3 |
|  6 | par2 |         3 |
|  7 | par3 |         3 |
|  8 | par1 |         4 |
|  9 | par2 |         4 |
| 10 | par1 |         6 |
| 11 | par2 |         6 |
+----+------+-----------+

parameterLog:
+-------------+-----------+-------+-------+
| action_date | parameter | value | notes |
+-------------+-----------+-------+-------+
| 2010-01-01  |         1 | 0     | NULL  |
| 2010-02-03  |         1 | 4     | NULL  |
| 2010-01-01  |         2 | 0     | NULL  |
| 2010-01-01  |         3 | 1     | NULL  |
| 2010-01-01  |         4 | 1     | NULL  |
| 2010-01-14  |         5 | 0     | NULL  |
| 2010-01-14  |         6 | 1     | NULL  |
| 2010-01-14  |         7 | 2     | NULL  |
| 2010-01-14  |         8 | 3     | NULL  |
| 2010-02-03  |         8 | 1     | NULL  |
| 2010-01-14  |         9 | 4     | NULL  |
| 2010-02-03  |         9 | 1     | NULL  |
| 2010-02-25  |        10 | 9     | NULL  |
| 2010-02-25  |        11 | 9     | NULL  |
+-------------+-----------+-------+-------+

emrhzc · Answer 1 · 2016-06-10T21:14:53.720

0

SELECT param.*,
       plog.action_date,
       plog.value,
       plog.notes 
FROM parameters param,parameter_log plog 
WHERE param.id=plog.parameter 
      and action_date = (select max(action_date) 
                         from parameterLog 
                         where parameter = param.id) 
      and component = 1;

edited Jun 10 '16 at 21:14

answered Jun 10 '16 at 20:33

emrhzc

1,347
11
19

Thanks for your reply! This is giving me the correct fields, but includes entries from previous parameter values (e.g., par1 appears in the table twice, once at its old value, and once at its current value). – bharper Jun 10 '16 at 20:51
Can there be multiple log records with same parameter and exactly the same action_date? – emrhzc Jun 10 '16 at 20:55
No. Those the latter value would overwrite the prior. – bharper Jun 10 '16 at 20:58
Ok. I'll add one. What will the query look like then? And thanks again for the help. – bharper Jun 10 '16 at 21:13
Sorry for mistaking, you don't need extra id, since all dates seem to be distinctive. – emrhzc Jun 10 '16 at 21:20
Thanks for all your help. Unfortunately even with the id, the result still includes entries from previous parameter values (e.g., par1 appears in the table twice, once at its old value, and once at its current value). I may have to rethink how my tables are structured. – bharper Jun 10 '16 at 21:23

MySQL: Join table fields to related result set

1 Answers1