-------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
-----Original Message----- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2008 3:30 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Many to many to too many relation. I tried reading this and it took much brain power to do for free. I suggest breaking this down into the simplest possible example you can. Ideally have an create table statement, and insert with a small amount of data (as simple as possible), a simple psuedo code query and example output of what you want returned. On Mon, Sep 29, 2008 at 2:00 PM, Weston, Craig (OFT) <[EMAIL PROTECTED]> wrote: > Hello everyone. > > > > I have a complex situation. I have a list of related records (see > partial extract below) where I am trying to determine the unique lowest > number for each relation. This number should always be an AB number, > since all the MA numbers will always relate to an AB number. I need the > distinct, lowest values that relate to each other, not necessarily in > the same row. > .............. Rob, I agree this is pretty complex. I am having difficulty understanding how to express what I am trying to accomplish. I appreciate your willingness to consider further, (or at least watch me try to explain this further, which is valuable all by itself) --- table --- CREATE TABLE `test` (`Field1` varchar(50) collate latin1_general_ci default NULL, `Field2` varchar(50) collate latin1_general_ci default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `test` VALUES ('AB100687', 'AB100633'); INSERT INTO `test` VALUES ('AB100687', 'AB100661'); INSERT INTO `test` VALUES ('AB100687', 'AB100680'); INSERT INTO `test` VALUES ('AB100687', 'AB100681'); INSERT INTO `test` VALUES ('AB100687', 'AB100682'); INSERT INTO `test` VALUES ('AB100687', 'MA003343'); INSERT INTO `test` VALUES ('AB100687', 'AB100688'); INSERT INTO `test` VALUES ('AB100687', 'AB100690'); INSERT INTO `test` VALUES ('AB100687', 'AB100722'); INSERT INTO `test` VALUES ('AB100687', 'AB100731'); INSERT INTO `test` VALUES ('AB100687', 'AB100792'); INSERT INTO `test` VALUES ('AB100688', 'AB100687'); INSERT INTO `test` VALUES ('AB100688', 'MA003340'); INSERT INTO `test` VALUES ('AB100689', 'MA003351'); INSERT INTO `test` VALUES ('AB100690', 'AB100687'); INSERT INTO `test` VALUES ('AB100690', 'MA003354'); INSERT INTO `test` VALUES ('AB100690', 'AB100731'); --- psudocode --- Give me the alphanumerically lowest (or first) record of each association. Each record returned must be unique. --- results I am trying for --- AB100633 AB100689 --- Possibly useful commentary and thoughts --- The problem I have is that the table lists all associations twice. So records 'AB100687', 'AB100688' exist in relation as well as 'AB100688', 'AB100687'. Relational chains also exist, for example, 'AB100687', 'AB100633' are related, but there are a bunch of id's that are associated with 'AB100687' but not with 'AB100633'. However since 'AB100633' is the "earliest" they all should relate to it. MA numbers will always be discarded since they are always related to an AB number, which will be lower. --- Thank you --- Thanks for your help. I know this is complex, and I am explaining it poorly, and I am sorry I can't express myself any better. I just can't comprehend it very easily. Thank you for reading this, even if you can't help. I feel like I learned something just trying to explain this situation. Regards, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
