List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:September 30 2008 2:11pm
Subject:Re: Many to many to too many relation.
View as plain text  
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)
<Craig.Weston@stripped> 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:wultsch@stripped]
> Sent: Tuesday, September 30, 2008 3:30 AM
> To: Weston, Craig (OFT)
> Cc: mysql@stripped
> 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)
> <Craig.Weston@stripped> 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
wultsch@stripped
wultsch (aim)
Thread
Many to many to too many relation.OFT)29 Sep
  • Re: Many to many to too many relation.Rob Wultsch30 Sep
    • RE: Many to many to too many relation.OFT)30 Sep
      • Re: Many to many to too many relation.Peter Brawley30 Sep
        • Re: Many to many to too many relation.Rob Wultsch30 Sep
      • Re: Many to many to too many relation.Rob Wultsch30 Sep
        • RE: Many to many to too many relation.OFT)30 Sep
        • RE: Many to many to too many relation.OFT)30 Sep
          • Re: Many to many to too many relation.Rob Wultsch30 Sep