Yes, agreed. The case you mentioned about missing tolerance is
not in his example though - we clearly need more input from Roy.
Jacek
> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
> Sent: Tuesday, October 04, 2005 12:56 PM
> To: Becla, Jacek
> Cc: Roy Harrell; mysql@stripped
> Subject: RE: Query Question
>
> Jacek,
>
> Your method would only work so long as each PartA, PartB, and
> PartC all
> have the same tolerance numbers. if PartA and PartB had a
> tolerance of 20
> but PartC didn't, your query would not show just the A and B
> tolerances.
> In fact, it wouldn't show a line for Tolerance 20 at all.
>
> The only way to do this in the pattern you describe is with
> the FULL OUTER
> JOIN predicate. MySql currently supports the INNER, LEFT, RIGHT, and
> NATURAL joins but not the FULL OUTER JOIN.
>
> There is a workaround for FULL OUTER JOIN if you need it but
> you have to
> be on a version that supports UNION queries to make it work
> without a temp
> table. The workaround also becomes very cumbersome if you are
> joining more
> than two tables.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> "Becla, Jacek" <becla@stripped> wrote on 10/04/2005
> 03:33:04 PM:
>
> > Hi,
> >
> > One way of doing it would be:
> >
> > select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles
> > as PartC from t as a, t as b, t as c where a.tolerance=b.tolerance
> > and a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B'
> > and c.PartName='C';
> >
> > Jacek
> >
> >
> > > -----Original Message-----
> > > From: Roy Harrell [mailto:1600uVision@stripped]
> > > Sent: Tuesday, October 04, 2005 12:16 PM
> > > To: mysql@stripped
> > > Subject: Query Question
> > >
> > > Suppose I have a simple table as follows:
> > >
> > > PartName Tolerance Cycles
> > > A 1 10
> > > A 2 11
> > > A 3 13
> > > A 4 15
> > > A 5 18
> > > B 1 12
> > > B 2 14
> > > B 3 16
> > > B 4 16
> > > B 5 17
> > > C 1 6
> > > C 2 7
> > > C 3 7
> > > C 4 8
> > > C 5 10
> > >
> > >
> > > How do I set up a query whose output would
> > > look like this:
> > >
> > > Tolerance PartA PartB PartC
> > > 1 10 12 6
> > > 2 11 14 7
> > > 3 13 16 7
> > > 4 15 16 8
> > > 5 18 17 10
> > >
> > >
> > > Thanks,
> > >
> > > Roy Harrell
> > > Adaptive Equipment
> > > 352.372.7821
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
>
