List:General Discussion« Previous MessageNext Message »
From:Craig (OFT) Weston Date:September 30 2008 12:16pm
Subject:RE: Many to many to too many relation.
View as plain text  
--------------------------------------------------------
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

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