"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 10/07/2005 02:57:28 PM:
> 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? > > Thanks, > Jon > > You cannot get that kind of results as distinct and separate columns. Have you looked at the GROUP_CONCAT() function as a work around? SELECT t1.date, t1.val, GROUP_CONCAT(t2.val) FROM t1 RIGHT JOIN t2 on t1.date = t2.date where t2.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me' GROUP BY t1.date, t1.val; Shawn Green Database Administrator Unimin Corporation - Spruce Pine