Query where a number is not in a column?

Question

I have an sql database with a table holding users(UserID, Name), a table holding events(EventID, EventName), a table holding roles(RoleID, RoleName) and a table assigning roles to users for each event (assignID, EventID, UserID, RoleID).

Now I wanna get a list of all the events that do not contain a certain role, say every event without the roleID 1, but I don't want any rows with a different roleID. how would I do that?

Please clarify: "but I don't want any rows with a different roleID" — IsisCode
I don't want all the rows with roleID 2 or roleID 3, I want to know which events do not contain a roleID 1. — user1162628

stryba stryba · Accepted Answer · 2012-01-21T18:39:30

Something like this should do

SELECT * 
FROM events 
WHERE eventid NOT IN 
     (SELECT eventid FROM assign WHERE roleID=1)

Or if subqueries are not available

SELECT e.* 
FROM events e 
  LEFT JOIN assign a ON (e.eventid=a.eventid AND a.roleid=1) 
WHERE a.roleid IS NULL

One more equivalent query:

SELECT * 
FROM events e 
WHERE NOT EXISTS
     (SELECT * FROM assign a WHERE a.eventid=e.eventid AND roleID=1)

Query where a number is not in a column?

1 Answers