zosokat — 2014-04-09T15:43:02-04:00 — #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:
r937 — 2014-04-09T22:42:05-04:00 — #2
this is not going to be efficient because it has to examine all rows of the table
HAVING COUNT(CASE WHEN userid = 'abc123'
ELSE NULL END) = 0
zosokat — 2014-04-11T15:06:11-04:00 — #3
Yes I found that out. It takes a long time to execute that query.
r937 — 2014-04-11T15:25:04-04:00 — #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
kylewolfe — 2014-04-11T15:25:22-04:00 — #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.
propid not in (
select distinct propid from user_assignments where userid = 'foo'
r937 — 2014-04-11T15:26:52-04:00 — #6
sadly, it might not
r937 — 2014-04-11T16:03:09-04:00 — #7
on the other hand, it might work as good as
unlikely it would be gooder