I have a table containing project assignments represented by a projectid column and a user_assignment column which contains the user's unique userid. A user is assigned to a project if they have a record in the user assignment table associated with the project id. I want retrieve all the projects that a specific user is NOT assigned to without getting duplicate records since there are many users assigned to projects.
While these examples will retrieve only one record per project (i.e., no duplicates), it returns projects that user 'abc123' is and is NOT assigned to. I need to retrieve the projects that they are NOT assigned to.
SELECT DISTINCT `propid` FROM `user_assignments` WHERE `userid` <> 'abc123' ORDER BY `propid` ASC
SELECT DISTINCT `propid` FROM `user_assignments` WHERE (`userid` <> 'abc123') ORDER BY `propid` ASC
I am sure there is a very simple solution but I am not seeing it.
The user assignment table: