List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:October 10 2005 7:44am
Subject:Re: Joining tables, duplicating none
View as plain text  
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

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