Hello.
> 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'; Maybe it is better to move a condition on t1 fields from WHERE to ON part of the query? Or I don't see any sense in using a RIGHT join in your query. > 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. How many columns do you want to have in a new table? MySQL is unable to dynamically change the number of the columns in the table. For your purposes GROUP_CONCAT should be helpful. See: http://dev.mysql.com/doc/mysql/en/group-by-functions.html An example query is: mysql> select t1date,t1val, group_concat(t2val) from t1 group by t1date, t1val ; +------------+-------+---------------------+ | t1date | t1val | group_concat(t2val) | +------------+-------+---------------------+ | 2005-08-01 | 92 | 18.3,23.3 | +------------+-------+---------------------+ mysql> select * from t1; +------------+-------+-------+ | t1date | t1val | t2val | +------------+-------+-------+ | 2005-08-01 | 92 | 18.3 | | 2005-08-01 | 92 | 23.3 | +------------+-------+-------+ 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? > > Thanks, > Jon > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]