SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    projects user NOT assigned to without getting duplicate records

    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.
    Code:
    SELECT DISTINCT `propid` FROM `user_assignments` WHERE `userid` <> 'abc123' ORDER BY `propid` ASC
    Code:
    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:

    pgs.user_assignments_table.PNG
    Attached Images Attached Images
    Last edited by Mittineague; Apr 9, 2014 at 14:48.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    this is not going to be efficient because it has to examine all rows of the table
    Code:
    SELECT propid 
      FROM user_assignments 
    GROUP
        BY propid
    HAVING COUNT(CASE WHEN userid = 'abc123' 
                      THEN 'nope'
                      ELSE NULL END) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is not going to be efficient because it has to examine all rows of the table
    Code:
    SELECT propid 
      FROM user_assignments 
    GROUP
        BY propid
    HAVING COUNT(CASE WHEN userid = 'abc123' 
                      THEN 'nope'
                      ELSE NULL END) = 0
    Yes I found that out. It takes a long time to execute that query.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ZosoKat View Post
    Yes I found that out. It takes a long time to execute that query.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ZosoKat View Post
    Yes I found that out. It takes a long time to execute that query.
    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.

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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    ... but might a 'not in' work better here
    sadly, it might not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by r937 View Post
    sadly, it might not
    on the other hand, it might work as good as

    unlikely it would be gooder

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •