CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Arti
Arti

Reputation: 3071

Select records based on other column condition

I have a SQL table structure like:

ID UID  Name   PId  Date
1   2   Admin   1   2015-04-25 21:10:40.060
3   28  Admin   1   2015-04-25 21:10:53.873
4   3   Viewer  1   2015-04-25 22:49:12.497
5   7   Admin   1   2015-04-25 22:49:34.670
6   28  Admin   2   2015-05-04 10:41:17.000

ID-> autoincrement, UID->UserId, PID-> projectId

Here a user can be assigned to one or multiple projects. So, I have user 28 assigned to two projects PID:1 and 2.

I want all the unassigned users to project 2. (i.e my result should contain UID 2, 3 and 7). And for project 1 it would be none.

Any help would be appreciated.

EDIT

This is what I tried:

select UId 
from A 
group where PId!=2 by uId having COUNT(*)<2

and the data I am getting is:

   UID  PID
    2   1
    3   1
    7   1
    28  1

The last record UID=28 is unwanted row

Upvotes: 1

Views: 43

Answers (1)

sqluser
sqluser

Reputation: 5672

You can use NOT IN operator in your WHERE clause

SELECT *
FROM yourtableName
WHERE UID NOT IN (SELECT UID
                  FROM #test
                  WHERE PID = 2)

SQLFiddle

Upvotes: 2

Related Questions