List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 7 2005 8:25pm
Subject:Re: Joining tables, duplicating none
View as plain text  
"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
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