[SQL] Related table question

[Eric Forget]

Hi Ruslan,

First, thank you very much for the time you took to resolve those issues.
And that during a weekend!

To summarize, I'm planning to use these rules:

1) From the master SELECT I never do a FROM Keyword.

2) I always write the keyword clauses either:

    a) For equality:
            a.RecID in (select ptr from keyword where name = 'Me')

    b) For inequality:

            a.RecID not in (select ptr from keyword where name <> 'Me')


However, I see b) working without understanding it. For me it looks like a
double negation. Maybe there is something about SQL I don't understand.

I've tried with all the 12 samples I gave you. They all works except 9) and
10) which deals with '' strings.

>> 9. (Keyword.Name = "")
>> 
>>   Result:     CalcID      Label
>>               3           7
>>               4           6
>> 
>> 10. (Keyword.Name <> "")
>> 
>>   Result:     CalcID      Label
>>               1           2
>>               2           3

Finally, I've cases where that do not works:

13)
    a.RecID in (select ptr from keyword where name = 'Me')
AND
    a.RecID in (select ptr from keyword where name = 'YOU')

    Result:     CalcID      Label

Instead of:     CalcID      Label
                1           2

14)
    a.RecID in (select ptr from keyword where name = 'Me')
OR
    a.RecID in (select ptr from keyword where name = 'YOU')

    Result:     CalcID      Label
                1           2

Instead of:     CalcID      Label
                1           2
                2           3

15)
    a.RecID in (select ptr from keyword where name = 'Me')
OR
    a.RecID not in (select ptr from keyword where name <> 'YOU')

    Result:     CalcID      Label
                1           2
                3           7
                4           6

Instead of:     CalcID      Label
                1           2
                2           3
                3           7
                4           6

Eric

___________________________________________________________________

 Eric Forget                       Cafederic
 ForgetE at cafederic.com             <http://www.cafederic.com/>

 Fingerprint <86D5 38F5 E1FD 5D9C 71C3  BAA3 797E 70A4 6210 C684>