help forming a query! :)

[m4trix]

Ok, here's the set-up.
I have two tables, for simplicty, we'll say
users and staff

the important fields are
users.userid
users.staffid

and
staff.staffid
staff.has_user (enum('0','1'))

All users must have an associated staff id (hence the users.staffid) but not all staff needs an associated userid, so the staff table has boolean field, "has_user", that is 1 if a user points to it, or 0 if not.

so for the sake of example, let's say we have:
[tt]
user1 -> user.id = 1 and user.staffid=1
user2 -> user.id = 2 and user.staffid=2
user3 -> user.id = 3 and user.staffid=3
user4 -> user.id = 4 and user.staffid=4
staff1 -> staff.staffid=1 and staff.has_user='1'
staff2 -> staff.staffid=2 and staff.has_user='1'
staff3 -> staff.staffid=3 and staff.has_user='1'
staff4 -> staff.staffid=4 and staff.has_user='1'
staff5 -> staff.staffid=2 and staff.has_user='0'
[/tt]

Now, here's the problem. Let's say I delete a user. I want to change the "has_user" field for the staff associated with that user to '0'. Ordinarily that would not be a problem - I would just grabe the staffid from the user before deleting it, and change the 'has_user' field like that. (and I may end up doing that if I can't get this other thing to work). Instead - users are deleted first without noting the associated staff id's.

SO, I was wondering if I can grab all staffids from the staff table where 'has_user' = '1' BUT staff.staffid is not in the user table.

So what I'm looking for is the INVERSE of
"SELECT s.staffid FROM staff s, users u WHERE s.has_user = '1' AND s.staffid = u.staffid"

that will return all staffid's that a user points to.
so if I delete user1 and user3
it will return:
staff2 (staff2-staffid=2) and
staff4 (staff4-staffid=4)

I'm looking for all staffid's that no users point to BUT where has_id='1'
or with my example:
staff1 (staff1-staffid=1) and
staff3 (staff3-staffid=3)

unfortunately, the intuative
"SELECT s.staffid FROM staff s, users u WHERE s.has_user = '1' AND s.staffid != u.staffid" query does not work because it will return:
[tt]
staff1 (staff1-staffid=1 because user2-staffid=2 (not 1))
staff1 (staff1-staffid=1 because user4-staffid=4 (not 1))
staff2 (staff2-staffid=2 because user4-staffid=4 (not 2))
staff3 (staff3-staffid=3 because user2-staffid=2 (not 3))
staff3 (staff3-staffid=3 because user4-staffid=4 (not 3))
staff4 (staff4-staffid=4 because user2-staffid=2 (not 4))
[/tt]

as you can see, as the number of users/staff increases, there will be more and more combinations that will be returned. I could make life much easier for myself doing this the other way - but I'm curious if mysql will provide a work-around to make this method work.

 

[m4trix]

I found a solution... It required upgrading my mysql DB from 4.0 to 4.1 - but here it is:

"SELECT staffid FROM staff s WHERE NOT EXISTS (SELECT u.staffid FROM users u WHERE u.staffid = s.staffid) AND s.has_user='1'"

brilliant

 

[m4trix]

EDIT: oops, bit off, here we go:
"SELECT DISTINCT `staffid` FROM `staff` WHERE NOT EXISTS (SELECT * FROM `users` WHERE users.`staffid` = staff.`staffid`) AND `has_user` = '1';