List:General Discussion« Previous MessageNext Message »
From:Roy Harrell Date:October 4 2005 9:01pm
Subject:RE: Query Question
View as plain text  
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
> > > 
> > 

Thread
Query QuestionRoy Harrell4 Oct
  • Re: Query QuestionDavid Griffiths4 Oct
  • Re: Query QuestionSGreen4 Oct
  • Re: Query QuestionPeter Brawley5 Oct
RE: Query QuestionJacek Becla4 Oct
  • RE: Query QuestionSGreen4 Oct
RE: Query QuestionJacek Becla4 Oct
  • RE: Query QuestionRoy Harrell4 Oct