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