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