List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:September 30 2008 7:30am
Subject:Re: Many to many to too many relation.
View as plain text  
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.
>
>
>
> What I have so far is:
>
>
>
> SELECT distinct
>
>       if (test.Field1 < test.Field2,test.Field1,test.Field2)
>
>          AS `Unique Incident id`
>
>
>
>   FROM  test
>
>
>
>
>
> The problem is that the data is recursive and non-exclusive, by which I
> mean that the data will always be in both columns, and it is possible
> that more than one relation exists.  In the case of more than 1 relation
> I still need the just lowest distinct value in the relation, no matter
> how many other members of the relation exist.
>
>
>
>
>
> Can anyone offer any suggestions?
>
>
>
> Here is some sample data to chew on.
>
>
>
> AB100674,MA003296
>
> AB100675,MA003306
>
> AB100676,MA003314
>
> AB100677,MA003312
>
> AB100678,MA003321
>
> AB100679,MA003318
>
> AB100680,MA003320
>
> AB100680,AB100687
>
> AB100681,MA003335
>
> AB100681,AB100687
>
> AB100682,MA003330
>
> AB100682,AB100687
>
> AB100683,MA003337
>
> AB100684,MA003336
>
> AB100685,MA003332
>
> AB100686,MA003341
>
> AB100687,AB100633
>
> AB100687,AB100661
>
> AB100687,AB100680
>
> AB100687,AB100681
>
> AB100687,AB100682
>
> AB100687,MA003343
>
> AB100687,AB100688
>
> AB100687,AB100690
>
> AB100687,AB100722
>
> AB100687,AB100731
>
> AB100687,AB100792
>
> AB100688,AB100687
>
> AB100688,MA003340
>
> AB100689,MA003351
>
> AB100690,AB100687
>
> AB100690,MA003354
>
> AB100690,AB100731
>
> AB100691,MA003353
>
>
>
> Thanks,
>
> Craig
> --------------------------------------------------------
> 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.
>



-- 
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