<p>I have a many to many index table, and I want to do an include/exclude type query on it.</p>

<p>fid is really a integer index, but here as letters for easier understanding.  Here's a sample table :</p>

<p>table t</p>

<pre><code>eid | fid
----+----
1   | A
1   | B
1   | C
2   | B
2   | C
3   | A
3   | C
4   | A
4   | B
5   | B
</code></pre>

<p>Here are some sample queries I want.</p>

<ol>
<li>What eids have fid B, and NOT A?  (Answer eid 2 and 5)</li>
<li>What eids have fid C, and NOT A?  (Answer eid 2)</li>
</ol>

<p>I can't seem to figure out a query that will do this.  </p>

<p>I've tried a self join like this:</p>

<pre><code>select * 
from t as t1 
join t as t2 
where t1.eid=t2.eid 
  and t1.fid!=t2.fid 
  and t1.fid=B and t2.fid!=A
</code></pre>

<p>That won't work, because it will still return rows where eid=1 and fid=C.</p>

<p>Am I clear on what I want?</p>

Use
set subtraction
```
```
Select eid from t where fid = 'B' 
EXCEPT
select eid from t where fid = 'A'
```
```