List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:October 7 2005 7:47pm
Subject:Re: Joining tables, duplicating none
View as plain text  
----- Original Message ----- 
From: <SGreen@stripped>
To: "Jonathan Mangin" <jon.mangin@stripped>
Cc: <mysql@stripped>
Sent: Friday, October 07, 2005 2:09 PM
Subject: Re: Joining tables, duplicating none


> "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

Thanks Shawn,

Interesting, but that won't work for me.
Are two separate selects my only choice?

I tried (with two different tables) including t1.id
in related records of Table 2 and ran into the same
problem.  With all the talk of normalization, I
thought this would be easier.  Am I leaving something
obvious out of the table designs?

--Jon

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