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: [email protected]
> 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]
