"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 10/07/2005 03:47:48 PM:
> > ----- 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 > I don't think your design is broken, I think how you wanted to see your data is non-standard. If I understand your question about two selects, I would probably say "yes". One query from table1 returning one row, a second from table2 returning 7 rows. Now, if they are actually related bits of information (like a phone number on table1 and additional phone numbers from table2, where you are using table2 to store all of your "extra" items) then it makes sense to use a UNION to make just one list out of both tables. I guess it all depends on what the data actually means whether this makes sense or not. ( SELECT t1.date, t1.val from table1 t1 INNER JOIN table2 t2 ON t1.date = t2.date where t2.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me' ) UNION ( SELECT t2.date, t2.val from table1 t1 INNER JOIN table2 t2 ON t1.date = t2.date where t2.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me' ); Shawn Green Database Administrator Unimin Corporation - Spruce Pine