Should have specified - all parts will have the same
tolerance numbers. Thanks for the quick, informative,
responses. I'll give them a whirl.
Roy
On Tue, 2005-10-04 at 15:59, Becla, Jacek wrote:
> 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
> > >
> >
