## Finding records that have no values in common

## Finding records that have no values in common

(OP)

I have a table, below, where each record has 3 distinct values between 1 and 9.

So, each of the three IDs must have NO VALUES IN COMMON.

For example, in the table above, ID values 1, 3, and 5 hold all numbers 1 to 9.

My current sql solves the problem correctly, but it is unwieldy. (especially if I scale the problem up to, say,

5 IDs that show ALL numbers from 1 to 15).

Here's my current sql...

Is there a more efficient way to do this? Thank you in advance for any hints.

Teach314

#### CODE

ID v1 v2 v3 --------------------- 11 6 22 4 8 1 33 5 44 3 8 5 58 7 96 etc...

**I want to identify all sets of three IDs that, collectively, hold ALL values from 1 to 9.**So, each of the three IDs must have NO VALUES IN COMMON.

For example, in the table above, ID values 1, 3, and 5 hold all numbers 1 to 9.

My current sql solves the problem correctly, but it is unwieldy. (especially if I scale the problem up to, say,

5 IDs that show ALL numbers from 1 to 15).

Here's my current sql...

#### CODE

SELECT t1.ID AS ID1, t2.ID AS ID2, t3.ID AS ID3 FROM Tbl AS t1, Tbl AS t2, Tbl AS t3 WHERE (t1.v1 <> t2.v1) AND (t1.v1 <> t2.v2) AND (t1.v1 <> t2.v3) AND (t1.v2 <> t2.v1) AND (t1.v2 <> t2.v2) AND (t1.v2 <> t2.v3) AND (t1.v3 <> t2.v1) AND (t1.v3 <> t2.v2) AND (t1.v3 <> t2.v3) AND (t1.v1 <> t3.v1) AND (t1.v1 <> t3.v2) AND (t1.v1 <> t3.v3) AND (t1.v2 <> t3.v1) AND (t1.v2 <> t3.v2) AND (t1.v2 <> t3.v3) AND (t1.v3 <> t3.v1) AND (t1.v3 <> t3.v2) AND (t1.v3 <> t3.v3) AND (t2.v1 <> t3.v1) AND (t2.v1 <> t3.v2) AND (t2.v1 <> t3.v3) AND (t2.v2 <> t3.v1) AND (t2.v2 <> t3.v2) AND (t2.v2 <> t3.v3) AND (t2.v3 <> t3.v1) AND (t2.v3 <> t3.v2) AND (t2.v3 <> t3.v3) AND (t1.ID < t2.ID) AND (t1.ID < t3.ID) AND (t2.ID < t3.ID) ORDER BY t1.ID;

Is there a more efficient way to do this? Thank you in advance for any hints.

Teach314

## RE: Finding records that have no values in common

The query:

SELECT T_1.ID AS T1_ID, T_2.ID AS T2_ID, T_3.ID AS T3_ID, Count(T_1.ID) AS N

FROM Tbl2 AS T_1, Tbl2 AS T_2, Tbl2 AS T_3

WHERE (((([T_1].[V]=[T_2].[V] Or [T_1].[V]=[T_3].[V] Or [T_2].[V]=[T_3].[V]))=False))

GROUP BY T_1.ID, T_2.ID, T_3.ID

HAVING (((T_2.ID)>[T_1].[ID]) AND ((T_3.ID)>[T_2].[ID]) AND ((Count(T_1.ID))=27));

returns IDs

Scaling:

[Tst] field has all 2-elements combinations, here 3 conditions. For k=5 elements for each ID there are 10 test conditions. I would create a simple VBA function instead,with paramarray input for testing, in this case max k-1 tests in chain.

EDIT:

Of course the number in last sentence is not true, max number of tests is k(k-1)/2.

combo

## RE: Finding records that have no values in common