Peter.
I did not ask the question. Go back to jail, do not pass go, do not
collect $200.
Craig,
This problem sucks is kinda fun. Take a look at windowing functions.
MySQL does not have them, but that would probable be the right way to
deal with this. For MySQL I think something like the following would
work.
I would suggest the following approach:
1. Create a temporary table to figure out the association. Something
like (groupid int, value varchar(50)
2. Open cursor: select distinct field1, field2 from test1
3. For each record query the the temp table to see if either value exists.
If (both fields do not already exist)
then insert a record both field1 and field2 values with the same
(new) group number.
elseif( one field is new )
insert the new field into the temp table with the group number of
other (already existing) field
else( both fields already exists)
then insert nothing
once that is done, all you would need to do is group by the groupid,
and select min(value).
I have some work to do, but if you can't get it going by this
afternoon, give me a yell and I could write out the stored
procedure...
On Tue, Sep 30, 2008 at 6:01 AM, Peter Brawley
<[EMAIL PROTECTED]> wrote:
> 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:[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
>
>
>
>
> ________________________________
>
> 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
>
>
--
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]
