Query Question
Suppose I have a simple table as follows: PartNameTolerance 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]
RE: Query Question
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:56 PM To: Becla, Jacek Cc: Roy Harrell; mysql@lists.mysql.com 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 [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost Connection to MySQL server during Data Load
I'm trying to import data in a sparsely populated table from a text file. I've attempted this with mysqlimport and the LOAD DATA command. In each case I get an ERROR 2013: Lost connection to MySQL server during query. My tables are InnoDB type and the database is small. I'm running Fedora Linux 2.6.6-1.435 and mysql server 4.0.20-standard. Thanks, Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Foreign Keys
Can a child table have multiple foreign key references linking its records to two or more parent tables? Can a parent table also be a child table? That is, can a parent table have a foreign key that links it to another table? I setting up my tables as INNODB types. Thanks, Roy Harrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relational Integrity
I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? Thanks in advance for any advice. Sincerely, Roy Harrell Adaptive Equipment 2512 NE 1st Blvd #400 Gainesville, FL 32609 352.372.7821 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
