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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > Sent: Tuesday, October 04, 2005 12:16 PM > > To: mysql@lists.mysql.com > > 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/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
