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