----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Friday, October 07, 2005 2:09 PM
Subject: Re: Joining tables, duplicating none
"Jonathan Mangin" <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
