I have two tables, users (t1) and user_attributes (t2). Each attribute in t2 has a name, value, start_date and end_date. The most current attribute has an end_date of null.
I am using left join to combine attributes with users.
select t1.name, ta1.value as attr1, ta2.value as attr2
from t1 LEFT JOIN
(select attr_value from t2 where attr_name = 'attr1') as ta1 LEFT JOIN
(select attr_value from t2 where attr_name = 'attr2') as ta2
I want to generate a table which includes a record for each day that an attribute changed:
+=========+===========+==========+==========+
| date | t1.name | attr1 | attr2 |
+=========+===========+==========+==========+
| 071010 | foo | bar | baz |
+---------+-----------+----------+----------+
| 071020 | foo | zzz | baz |
+---------+-----------+----------+----------+
| 071030 | foo | zzz | zzz |
+---------+-----------+----------+----------+
Then as I loop through a date range in my application, I can just select the date less than the date pointer to retrieve the attribute values for that date.
Questions: is there an easier way to turn a 1-to-many relationship into a table like this (like a pivot table)? And, is there a sub-select or aggregate or something I can do to produce results like the above? Has anyone done something similar to the above? (I am actually using SQL Express but any insight in MySQL would help too.)
