Hi Jonathan, all!
Jonathan Mangin wrote:
I have two tables with date and uid cols. in common.
Table 1 has one row per date, Table 2 has a maximum
of 7 rows per date.
select t1.date, t1.val, t2.val from t1
right join t2 on t1.date = t2.date
where t1.date between '2005-08-01' and '2005-08-14'
and t1.uid = 'me';
+------------------------------+
| t1.date | t1.val | t2.val |
+------------+--------+--------+
| 2005-08-01 | 92 | 18.3 |
| 2005-08-01 | 92 | 23.3 |
+------------+--------+--------+
Is there any way to get something like this
+------------------------------+--------+
| t1.date | t1.val | t2.val | t2.val |
+------------+--------+--------+--------+
| 2005-08-01 | 92 | 18.3 | 23.3 |
+------------+--------+--------+--------+
instead of duplicating Table 1 rows for every row
in Table 2?
Others have proposed GROUP_CONCAT.
IMO, you need to specify your desires more detailed:
If "t2" contains several rows matching one row of "t1" on the "date"
value, which values of "t2" do you want to see?
Would grouping and applying functions to the group solve your needs,
like below?
select t1.date, t1.val, min(t2.val), max(t2.val), count(t2.val)
from t1
right join t2 on t1.date = t2.date
where t1.date between '2005-08-01' and '2005-08-14'
and t1.uid = 'me'
group by t2.date
If not, it might be necessary that you do the conversion from up to 7
rows to up to 7 columns for "t2.val" in your application.
Another common way to present such data is by using a report generator
(not repeating the fields which did not change from the previous line),
whose output might look like this:
+------------------------------+
| t1.date | t1.val | t2.val |
+------------+--------+--------+
| 2005-08-01 | 92 | 18.3 |
| | | 23.3 |
+------------+--------+--------+
To answer the possible follow-up question: No, I do not know a specific
product which would do this for MySQL.
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
