Query to check if many-to-many relation exists

I开发者_如何学Go have one of these moments when something seems so simple but I just can't seem to find the right query. I am looking for a query that checks if a certain many-to-many relation already exists. Consider the following table:

    +---------+---------+
    | feed_id | coll_id |
    +---------+---------+
    |       1 |       1 |
    |       1 |       2 |
    |       1 |       3 |
    +---------+---------+

It's a table from a many to many relation between a table 'feeds' and a table 'collections'.

I need a query that checks if a feed already exists that has collections 1 and 2, and ONLY 1 and 2. If there is, I would need its feed_id.

In the above table this feed does not exist.

A more generic description of the query would be: find a feed that contains all of the coll_id's, and that the feed doesn't contain any other collections. This would mean the following for the example above:

1. making sure that coll_id is IN(1,2)
2. making sure that a COUNT(*) WHERE feed_id = 1 would return 2 (the number of coll_id's)

What's problematic in step 2 is that I don't have the value of the feed_id available in my sub query.

Or am I completely on the wrong track?

Any help would be much appreciated!

I think this will answer all the situations:

SELECT coll_id, feed_id
FROM a a1
GROUP BY coll_id
HAVING feed_id IN (2,4)
AND COUNT(*) = 2
AND NOT EXISTS (
    SELECT * 
    FROM a a2
    WHERE a2.coll_id = a1.coll_id
    AND feed_id NOT IN (2,4)
)

I did my tests with these data:

+---------+---------+
| feed_id | coll_id |
+---------+---------+
|       1 |       1 |
|       2 |       4 |
|       2 |       7 |
|       3 |       3 |
|       4 |       2 |
|       4 |       4 |
|       5 |       4 |
|       6 |       1 |
|       6 |       2 |
|       6 |       4 |
|       7 |       2 |
|       7 |       4 |
+---------+---------+

Only coll_id 4 and 7 has only 2 and 4 as feed_id

I also did the test with feed_id (1,2,4) returns 6 and feed_id (3) returns 3

Patrick

I didn't test this query, and I assume you have a UNIQUE key on both columns:

SELECT feed_id
FROM t
WHERE feed_id = 1 AND coll_id IN (1,2)
GROUP BY feed_id
HAVING COUNT(coll_id) = 2