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