Trouble writing SQL to solve my problem 1

[VickyC]

Greetings to all

I've been struggling to write some SQL, but I'm at the point where I'm just going in circles. I'd really appreciate some assistance. My question is based on the following table, shown in part:

[b]tblGroupItemExclude[/b]
[u][b]G   I   X[/b][/u]            [u][b]G   I   X[/b][/u]            [u][b]G   I   X[/b][/u]
                    (cont'd)             (cont'd)
 .  .  .
 3   9   2           4  13   8            5  17  11
 3   9   9           4  13   9            5  17  12
 3   9  11           4  13  13            5  17  13
 3   9  14           4  13  18            5  17  17

 3  10  10           4  14   6            5  18  11
 3  10  11           4  14  14            5  18  12
 3  10  15           4  14  15            5  18  13
 3  10  16           4  14  20            5  18  18
 
 3  11   5           4  15   6            5  19  10
 3  11  11           4  15   9            5  19  11
 3  11  12           4  15  10            5  19  12
 3  11  17           4  15  15            5  19  19

 3  12  11           4  16   7            5  20   9
 3  12  12           4  16  10            5  20  10
 3  12  13           4  16  16            5  20  11
 3  12  16           4  16  20            5  20  12
                                          ... etc

I want to write a function that inputs 3 values of Group G from the table (repeats allowed)and outputs a query result showing all possible triplets of Item I.

As you can see in the table...
If G = 3, then I = 9, 10, 11 or 12.
If G = 4, then I = 13, 14, 15 or 16.
If G = 5, then I = 17, 18, 19 or 20.

So, f(5, 3, 4) will produce a query table of triplets like 17, 9, 13; 17, 9, 14; etc. It is easy to use Inner Joins of tblGroupItemExclude to itself to produce such a table.

Here's my problem. Column X shows values of I that are no longer available in a record once an I value is used.
For example, for the function f(5, 3, 4), the 1st output column can contain I = 17, 18, 19 or 20. But, if the 1st column holds a 17, then the 2nd or 3rd columns in that record cannot hold a 11, 12, 13 or 17. These values from the X column, and are excluded once 17 is used. So, 17 could only be followed by 9 or 10 (because 11, 12 are excluded). The 9, in turn can only be followed in the 3rd column of output by 13, 15 or 16 (the 9 excludes 14).

The full output for f(5,3,4) would be...

[b]I1   I2   I3[/b]
17    9   15
17    9   16
18    9   15
18    9   16
18   10   14
19    9   13
19    9   15

I'm sorry about the lengthy explanation, but I'm just trying to be clear. I would be very appreciative of any help with this problem. I need SQL that excludes the values in the X column in the way I've described.

Thank you
Vicky C.

 

[PHV]

Typed, untested:

SELECT A1.I AS I1,A2.I AS I2,A3.I AS I3
FRPM tblGroupItemExclude A1,tblGroupItemExclude A2,tblGroupItemExclude A3
WHERE A1.G=5 AND A2.G=3 AND A3.G=4
AND A2.I NOT IN(SELECT X FROM tblGroupItemExclude WHERE G=A1.G AND I=A1.I)
AND A3.I NOT IN(SELECT X FROM tblGroupItemExclude WHERE G=A1.G AND I=A1.I)
AND A3.I NOT IN(SELECT X FROM tblGroupItemExclude WHERE G=A2.G AND I=A2.I)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Hey PHV - as usual, your solution is very clear and instructive.

Thanks
Vicky C