Finding records that have no values in common 1

[teach314]

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

ID    v1    v2    v3
---------------------
 1     [b]1     6     2[/b]
 2     4     8     1
 3     [b]3     5     4[/b]
 4     3     8     5
 5     [b]8     7     9[/b]
 6     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...

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

 

[combo]

After normalizing your table (Get&Transform in excel, in access you can do it with union of a set of queries if it is your original structure) to Tbl2:
[pre]ID Col V
1 v1 1
1 v2 6
1 v3 2
2 v1 4
2 v2 8
2 v3 1
3 v1 3
3 v2 5
3 v3 4
4 v1 3
4 v2 8
4 v3 5
5 v1 8
5 v2 7
5 v3 9[/pre]
The query:
[tt]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));[/tt]
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

 

[teach314]

Excellent, combo. Very instructive!