can't figure out sql query

[Ruslan Zasukhin]

On 5/2/06 6:17 PM, "Robert Brenstein" <rjb at robelko.com> wrote:

Hi Robert,

> I am somehow dense today and can't figure out an SQL query :(
> 
> table 'users' has each user identified by 'userid'
> table 'students' has references to 'users' in column 'studentid'
> table 'teachers' has references to 'users' in column 'teacherid'
> 
> Yes, I am using the traditional keys.

 
> I am trying to find a count of users who are neither teachers nor students,
> that is to count records in 'users' that have no entries in tables 'students'
> and 'teachers', and I am drawing a blank.
> 
> Note that 'students' and 'teachers' may have multiple entries for a given user
> since these tables establish M:M relations between 'users' and 'courses'.

And this is using v1 ?

Sounds like query with EXCEPT

May be you can get it using

WHERE 
    RecID NOT IN 
        (select A.RecID FROM T1, T2 WHERE T1.id = T2.ptr )
And 
    RecID NOT IN 
        (select A.RecID FROM T1, T3 WHERE T1.id = T3.ptr )

Not sure how effective this will be.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]