From: Peter Brawley
Date: September 30 2008 1:01pm
Subject: Re: Many to many to too many relation.
List-Archive: http://lists.mysql.com/mysql/214705
Message-Id: <48E22314.1000704@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010301020703050107050901"

--------------010301020703050107050901
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rob,

 >I need the distinct, lowest values that relate to each other,
 >not necessarily in the same row.

First, the data representation does not appear to represent the problem 
adequately. Second, I do not understand what "relate to each other, nor 
necessarily in the same row" means. Third, I do not see, in what you 
post, a rule that would restrict the result to AB100633,AB100689 .

PB

-----

Weston, Craig (OFT) 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
>
>
>   
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.169 / Virus Database: 270.7.2/1690 - Release Date: 9/25/2008 7:05 AM
>
>   

--------------010301020703050107050901--