hi - I'm getting all tangled up try 1

[teach314]

hi - I'm getting all tangled up trying to write some SQL. my problem involves two tables shown below.

[b]TblA[/b]                                      [b]TblB[/b]

ID     Position       Val                  Val      RestrictedVal       
63         4           39                  .......
63         3           41                   38         26 
63         2           38                   38         34
63         1           [COLOR=blue]40[/color]                   38         64   
63         0           [COLOR=red]45[/color]                   
                                            39         21
72         4           38                   39         49  
72         3           41                   39         77
72         2           40
72         1           38                   40         31  
72         0           39                   [COLOR=blue]40[/color]         [COLOR=red]45[/color]
                                            40         51
98         4
98         3                                41         29 
... etc                                     41         35
                                            41         49

I need to SELECT all values of TblA.ID that pass the following test: Each Val in TblA cannot be FOLLOWED by a Restricted Val. Restricted Vals are shown in TblB.

For example, ID = 63 FAILS, because 40 (in blue) cannot be followed by 45 (in red). (TblB says that 40 can be followed by any number except 31, 45 or 51.)

On the other hand, ID = 72 PASSES, because none of its 'following' Vals are restricted.)

So, I need the SQL to SELECT all IDs like 72 that PASS.


much thanks in advance for any clues. I'm new enough to SQL that this problem is really throwing me for a loop.

Teach314

 

[PHV]

Something like this ?

SELECT DISTINCT A.ID
FROM tblA A LEFT JOIN (
SELECT A1.ID FROM (tblA A1 INNER JOIN tblA A2 ON A1.ID=A2.ID AND A1.Position=A2.Position+1)
INNER JOIN tblB B ON A1.Val=B.Val AND A2.Val=B.RestrictedVal
) R ON A.ID=R.ID
WHERE R.ID Is Null

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

 

[teach314]

Thanks PHV = I modified your code a bit, but you put me on the right track!