Many to many table queries

Question

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

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

table t

eid | fid
----+----
1   | A
1   | B
1   | C
2   | B
2   | C
3   | A
3   | C
4   | A
4   | B
5   | B

Here are some sample queries I want.

1. What eids have fid B, and NOT A? (Answer eid 2 and 5)
2. What eids have fid C, and NOT A? (Answer eid 2)

I can't seem to figure out a query that will do this.

I've tried a self join like this:

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

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

Am I clear on what I want?

Answer 1

Use set subtraction

Select eid from t where fid = 'B' 
EXCEPT
select eid from t where fid = 'A'

Answer 2

You can use a sub-select

select eid from t where fid = 'C' and eid not in (select eid from t where fid = 'A')

Answer 3

Here's an example of a query for 1 (2 works much the same)

select t1.eid
  from t t1
 where t1.fid  = 'B'
   and not exists
       (select 1
          from t t2
         where t2.eid = t1.eid
           and t2.fid  = 'A')

Answer 4

bugger. I just had to be using mysql that doesn't support that.

Thanks, I'll figure out how to emulate that.

Answer 5

MySQL 5.0 supports the where exists/where not exists, as described by Nigel and Mike.

Answer 6

Version with straight joins that may be faster than using EXISTS:

Select  t1.eid
From    #test t1
     left join (
      Select  eid
      From #test t2 
      Where fid = 'A'
      Group by eid
     ) t2 on t2.eid = t1.eid 
Where   t1.fid = 'B'
        and t2.eid is null

Answer 7

It should be possible to do this without using a subquery:

SELECT DISTINCT t1.eid
FROM table1 AS t1
  LEFT JOIN table1 AS t2 ON (t1.eid = t2.eid AND t2.fid = 'A')
WHERE t2.eid IS NULL
  AND t1.fid = 'B';

To do your second example search, just change the value 'B' to 'C'.

Answer 8

Look into the MINUS operator. It works like UNION, except that it subtracts where UNION adds. The previous answer with the word "EXCEPT" may be a different keyword for the same thing.

Here's an untested answer:

select eid 
from t
where fid = 'A'
minus
select eid
from t
where fid = 'B'