List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 7 2005 7:09pm
Subject:Re: Joining tables, duplicating none
View as plain text  
"Jonathan Mangin" <jon.mangin@stripped> 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
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