two tables not equal

Question

I have a SQL query and here it is the part I'm stuck on.

$query = "SELECT DISTINCT user.firstname, user.lastname, user.userid FROM user, staff WHERE user.type = 'Staff' AND staff.userid!= user.userid";

For some reason, it only prints out the part where users are staff but the != is not working, where as if I remove the ! and only have staff.user_id = user.user_id it works and prints out all users that are in both of those tables?

Can someone please explain why is this is happen and have a solution.

EDIT

TABLE USER                       TABLE STAFF
ID - NAME - TYPE                ID   -     NUMBER
1  - A    - Staff               1    -      11111
2  - B    - Staff               2    -      22222
3  - C    - Customer
4  - D    - Customer
5  - E    - Staff
6  - F    - Staff

How would I find the users id 5 and 6?

Answer 1

Try <> instead of !=.

Edited answer for the edited question:

SELECT user.firstname, user.lastname, user.userid 
FROM user LEFT JOIN staff ON user.userid=staff.userid 
WHERE user.type = 'Staff' AND staff.userid IS NULL ;

Answer 2

OK, based on your comment, I think this is what you want:

SELECT DISTINCT user.firstname, user.lastname, user.userid 
FROM user u
LEFT JOIN staff s ON (s.userid = u.userid)  
WHERE (s.userid is null) AND (u.type = 'Staff')

The left join will find all the records in user table (the left table) that have a matching userid and for those that don't.

So a modified query

SELECT * 
FROM user u
LEFT JOIN staff s ON (s.userid = u.userid)

would return

 
1  - A    - Staff               1    -      11111
2  - B    - Staff               2    -      22222
3  - C    - Customer            NULL NULL NULL
4  - D    - Customer            NULL NULL NULL
5  - E    - Staff               NULL NULL NULL
6  - F    - Staff               NULL NULL NULL

adding a partial WHERE clause

SELECT *
    FROM user u
    LEFT JOIN staff s ON (s.userid = u.userid)  
    WHERE (s.userid is null)

would return:

 
3  - C    - Customer            NULL NULL NULL
4  - D    - Customer            NULL NULL NULL
5  - E    - Staff               NULL NULL NULL
6  - F    - Staff               NULL NULL NULL

And finally the complete query would return

SELECT *
    FROM user u
    LEFT JOIN staff s ON (s.userid = u.userid)  
    WHERE (s.userid is null) AND (u.type = 'Staff')

would return:

 
5  - E    - Staff               NULL NULL NULL
6  - F    - Staff               NULL NULL NULL

Note: assuming the user table is unique, the 'DISTINCT' is redundant.