Rob, I tried this on the small test data, a larger data set and it appears to make my day. I sincerely appreciate your willingness to help me out.
I do have some questions, mostly so I can understand how this works better: I was wondering what the lines: set odone = done; set done = odone; do? I mean, in context, I understand they make variables do their thing, but I wasn't sure why they were needed. Also The statement set s_groupid =null; is very understandible, I just wondered why it was needed, or conversely why the other variables were not set null at the end? Thank you. -----Original Message----- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2008 10:11 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Many to many to too many relation. Luck for you my vpn appears to be screwed up. Here is a stored proc I whipped up: Stored procedure: DROP PROCEDURE IF EXISTS s; delimiter // CREATE PROCEDURE s () BEGIN DECLARE done INT DEFAULT 0; DECLARE odone INT DEFAULT 0; DECLARE f1,f2 VARCHAR(50); DECLARE maxgroup INT DEFAULT 0; DECLARE s_groupid INT; DECLARE cur1 CURSOR FOR SELECT DISTINCT Field1,Field2 FROM test.test; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp(groupid int,value varchar(50), UNIQUE(groupid, value)); OPEN cur1; REPEAT FETCH cur1 INTO f1, f2; IF NOT done THEN set odone = done; SELECT groupid INTO s_groupid FROM TEMP WHERE value IN(f1,f2) limit 1; set done = odone; IF s_groupid IS NULL THEN INSERT INTO temp values(maxgroup, f1); INSERT INTO temp values(maxgroup, f2); SET maxgroup = maxgroup+1; ELSE INSERT IGNORE INTO temp values(s_groupid, f1); INSERT IGNORE INTO temp values(s_groupid, f2); END IF; END IF; set s_groupid =null; UNTIL done END REPEAT; CLOSE cur1; END; // delimiter ; call s(); mysql> call s(); Query OK, 0 rows affected (2.61 sec) mysql> select min(value) from temp group by groupid; +------------+ | min(value) | +------------+ | AB100633 | | AB100689 | +------------+ 2 rows in set (0.00 sec) Which matches the expect below... On Tue, Sep 30, 2008 at 5:16 AM, Weston, Craig (OFT) <[EMAIL PROTECTED]> wrote: > -------------------------------------------------------- > 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 > > -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]