Filtering attached entities, AND & OR

The short answer is: No, you won't be able to do this with Views.

The long answer is to explain why this is hard in SQL; once you understand that, you'll likely see why this is hard in Views.

First, let's start with a simpler example than a node to fields, but one that represents the same kind of data structure. I have a table called organizations It has an organization id (we'll call it oid) some amount of data.

I then have another other table members which contains an oid (the organization the user is a member of) and some other data. An organization can have 0..infinity members (though fitting infinite members into your database is another problem!).

Now, I want to find every organization that 'Dave' and 'Jerad' are members of. (Apologies if to Dave and Jerad if they read this :). In order to find the organizations, I need to actually put my constraints in the relationships because of the way SQL works:

SELECT o.* FROM organizations o 
  LEFT JOIN members m1 ON m1.oid = o.oid AND m1.name = 'Dave' 
  LEFT JOIN members m2 ON m2.oid = o.oid AND m2.name = 'Jerad'
WHERE m1.oid IS NOT NULL AND m2.oid IS NOT NULL;

Sidenote: We could also do this with INNER JOINs which would be simpler, but I think this method does a better job of explaining what is going on.

What we're doing is joining the members table twice and telling SQL to only join the member record for a particular person. In SQL, if you perform a LEFT join and no records match in the right table, you still get a record and all the fields in the right table are NULL. So we filter those out. (This is what the INNER join would've done for us).

Sidenote 2: This query will fail if you can have Dave or Jerad as a member of the organization twice. Or rather, it will potentially produce duplicates. At that point you'll need a subquery. The subquery is less efficient, but more stable, and slightly more straightforward.

The subquery looks like this:

SELECT o.* FROM organizations o
WHERE o.oid IN (SELECT m.oid FROM members m WHERE m.name = 'Dave') AND o.oid IN (SELECT m2.oid FROM members m2 WHERE m2.name = 'Jerad');

As you can hopefully see, both of these queries are both non-trivial and don't follow the standard sort of template for the way Views generates queries. Views tries to avoid subqueries wherever possible (they're inefficient) and Views is simply incapable of creating the kind of relationship needed for the first query using field api.

You could, potentially, write a custom filter handler that would accomplish the second query. But Views can't do this on its own and is unlikely to be able to without custom coding.