"Jonathan Mangin" <jon.mangin@stripped> wrote on 10/07/2005 03:47:48
PM:
>
> ----- 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
>
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
