Projects user NOT assigned to without getting duplicate records

Databases
1

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:

2

this is not going to be efficient because it has to examine all rows of the table

SELECT propid
  FROM user_assignments
GROUP
    BY propid
HAVING COUNT(CASE WHEN userid = 'abc123'
                  THEN 'nope'
                  ELSE NULL END) = 0
3

Yes I found that out. It takes a long time to execute that query.

4

it’ll go a lot faster if there’s a composite index on propid,userid

those two should actually be the primary key, instead of an auto_increment, but since you’re using an auto_increment, then declare them as a UNIQUE index

5

I haven’t played in the world of SQL for awhile, but might a ‘not in’ work better here assuming working on indexed fields and a relatively reasonable workload for a single user.


select 
     distinct propid 
from 
     user_assignments 
where 
     propid not in (
           select distinct propid from user_assignments where userid = 'foo'
      )
6

sadly, it might not :wink:

7

on the other hand, it might work as good as

unlikely it would be gooder

:wink: