List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:October 7 2005 8:30pm
Subject:Re: Joining tables, duplicating none
View as plain text  
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
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
Joining tables, duplicating noneJonathan Mangin7 Oct
  • Re: Joining tables, duplicating noneSGreen7 Oct
    • Re: Joining tables, duplicating noneJonathan Mangin7 Oct
      • Re: Joining tables, duplicating noneSGreen7 Oct
  • Re: Joining tables, duplicating nonePeter Brawley7 Oct
  • Re: Joining tables, duplicating noneGleb Paharenko8 Oct
  • Re: Joining tables, duplicating noneJoerg Bruehe10 Oct