Retrieving DISTINCT Records

[JipThePeople]

Server version: 5.5.23 - MySQL Community Server

 

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, it returns projects that user 'abc123' is and is NOT assigned to. I need to retrieve the project ids that the user is 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 also tried a sub-query but it locks up mySQL in phpMyAdmin and never returns any records which is telling me there is something really wrong with this.

SELECT DISTINCT `propid` 
FROM `user_assignments` 
WHERE `propid` NOT IN (SELECT `propid` 
FROM `user_assignments` 
WHERE `userid` = 'abc123')
ORDER BY `propid`

I have limited experience with writing SQL so I would really appreciate any recommendations. I am sure there is a very simple solution but I am not seeing it.

Edited April 9, 2014 by JipThePeople

[requinix]

The column is propid and it's in the user_assignments table?

 

You don't need to DISTINCT anything. Select from the projects table (the one that has a list of all the projects) then LEFT JOIN it with the assignments table on the matching project ID and the correct user ID, then keep everything that failed to join.

SELECT p.id
FROM projects p
JOIN user_assignments ua ON p.id = ua.propid AND ua.userid = 'abc123'
WHERE ua.id /* or another column in that table */ IS NULL

If you're getting poor performance out of the query, that's a different problem...

 

[edit] Above should say LEFT JOIN. Like I did in the description before it.

Edited April 10, 2014 by requinix

[JipThePeople]

The column is propid and it's in the user_assignments table?

 

You don't need to DISTINCT anything. Select from the projects table (the one that has a list of all the projects) then LEFT JOIN it with the assignments table on the matching project ID and the correct user ID, then keep everything that failed to join.

SELECT p.id
FROM projects p
JOIN user_assignments ua ON p.id = ua.propid AND ua.userid = 'abc123'
WHERE ua.id /* or another column in that table */ IS NULL

If you're getting poor performance out of the query, that's a different problem...

 

 

Thanks for your response. I'm not sure a JOIN is required since all the data required is in the user_assignments table. I need a list of propids where user 'abc123' does not have a matching record for that project. All that info is contained in the user_assignments table.

 

Here is the user_assignments table.

CREATE TABLE IF NOT EXISTS `user_assignments` (  `id` int(10) 
unsigned NOT NULL AUTO_INCREMENT,  `userid` varchar(100) NOT NULL 
DEFAULT '',  `propid` int(10) unsigned NOT NULL DEFAULT '0',  
`security_level` int(10) unsigned NOT NULL DEFAULT '0',  `role` int(10) 
unsigned DEFAULT '0',  `custom_security` int(10) unsigned DEFAULT 
'0',  PRIMARY KEY (`id`)) ENGINE=MyISAM  DEFAULT 
CHARSET=latin1 ;

I ran the query you suggested and it returned an empty set which is not correct because there are projects not assigned to user 'abc123'. Does this additional information clarify my problem a little more? I really appreciate you taking the time to review my issue.

[Barand]

JOIN in the above query should be LEFT JOIN

[requinix]

JOIN in the above query should be LEFT JOIN

Yes. It totally should.

[JipThePeople]

Thx for all the replies. I got it worked out. I appreciate the help!