How do I get a LEFT OUTER JOIN in Views that displays all rows with no match in the LEFT JOIN-ed table?

Question

Views 7.x-3.x

Staff

sid name
1   Adam
2   Barbara
3   Carol
4   David


Staff Roles

sid rid
1   1
2   1
2   2
3   2
3   3

Let's say that I want a list of all staff, excluding staff who have a particular role, and that role id (rid) will be provided in the URL.

Let's say that the rid we want to exclude is 2. So we want our view to display Adam, David.

My approach has been to list all the staff in a view, then add the relationship to Staff Roles, put the rid into a contextual filter, and then say it is to be excluded.

Unfortunately, what this produces is a LEFT JOIN of all the rids except 2. So we get Adam, Barbara, Carol.

So you might think: let's not exclude the contextual filter for rid. Let's include it on a non-excluding LEFT JOIN. Then we can maybe filter for rows with a rid of NULL. Unfortunately, views puts the contextual filter into the WHERE clause of the query, so we end up with just Barbara and Carol.

So my question is:

How would you build a view to list all staff, excluding staff who have a particular role, where that role id (rid) will be provided in the URL?

Alternatively:

How can I get conditions from a contextual filter into the ON part of a view query, instead of the WHERE part?

Answer 1

In your RID contextual filter, if you expand the "More" fieldset at bottom of the options modal, you should see an "Exclude" option which will flip the filter operator to negate.