Not sure about complex query

[lovattj]

Hi

I am trying to use SQL to select rows which don't match certain criteria - let me explain!

There are three tables - committees, committeememberships and users.

committees holds details on committees - e.g. the name, a description
committeememberships holds details on who is a member of what committee using ID numbers - each committee has a unique ID as does each user.
users holds details on users.

I have successfully managed to extract a list of committees that a user IS a member of - SELECT * from committeememberships INNER JOIN committeememberships.committeeid=committees.committeeid where committeememberships.userid=x;

But I am confused as to how to extract a list of committees the user IS NOT a member of?

I thought:

SELECT * from committeememberships INNER JOIN committeememberships.committeeid=committees.committeeid where NOT committeememberships.userid=x;

But that simply returns a list of all other users and committee memberships (all combinations other than the WHERE NOT criteria)

Is what I'm asking possible?

Thanks :-)

[Hack]

Welcome to DevX

How about

SELECT * from committeememberships 
INNER JOIN committeememberships.committeeid=committees.committeeid 
where  committeememberships.userid <> x;

I find it odd there is no ON part of your JOIN

[lovattj]

Ah yes, there actually is but I mistyped! Sorry!

SELECT * from committeememberships INNER JOIN committees ON committeememberships.committeeid=committees.committeeid where committeememberships.userid=x;

is what gives me a list of current committees that the user belongs to.

I have tried what you suggested (slightly modified to include the ON argument so-

SELECT * from committeememberships
INNER JOIN committees ON committeememberships.committeeid=committees.committeeid
where committeememberships.userid <> x;
)

but all that does is returns every other committee membership except for ones from the user ID entered.

I am using ASP and I guess it could be done the web server end by something like:

Select * from committees
-For each x in committees
--Select * from committeememberships where userid=x and committeeid=y
---If EOF then must not be a member
---Else must already be a member
-Next x

But that doesn't seem particularly efficient?

Thank you for the welcome and any help :-)