How to obtain the number of registries in a table that specifically match the characteristics of a different registry in another table?

This is a problem that's had me stumped for a few days now. I'm not that proficient at SQL, so bear with me if this seems obvious.

I have two tables, one with ingredients a bit like so:

ITEM     INGREDIENTS
-----    -------------
A01      Ing-01
A01      Ing-02
A01  开发者_如何学编程    Ing-03
A02      Ing-01
A02      Ing-03
A02      Ing-05
A03      Ing-02
A03      Ing-12
A03      Ing-22
 . 
 . 
 . 
A99      Ing-04

So, say, item A01 has a specific set of three ingredients.

And then, there's another table , much larger, that includes information like this:

PACK      INGREDIENTS
-----     ------------
AAA       Ing-01
AAA       Ing-02
AAA       Ing-03
ABB       Ing-72
ABB       Ing-74
ABB       Ing-81
BCC       Ing-01
BCC       Ing-02
BCC       Ing-07
 . 
 . 
 . 
ZQY       Ing-02

The challenge here is that I need a quick way to determine how many packs have exactly the ingredients for a given item. So far I have to run a query to find the ingredient set for a given item and then run a separate query to count the number of packs that have EXACTLY that set of ingredients. So, I'm trying to put together a single query that gives me that information.

The problem becomes more complex because in some isolated cases I might need to know how many packs have AT LEAST two of the ingredients, so I have to build the query in such a way that I need only change it minimally in order to get the results.

Is it possible at all, or am I overreaching? Any and all help and suggestions will be deeply appreciated.

Regards,

---

if we assume that all items are made with three ingredients and all packs also have three ingredients exactly, you can easily find the matches between packs and items with this query:

SQL> SELECT p.pack, i.item, COUNT(*)
  2    FROM pack p
  3    JOIN item i ON p.ingredient = i.ingredient
  4   GROUP BY p.pack, i.item
  5  HAVING COUNT(*) >= 3;

PACK ITEM   COUNT(*)
---- ---- ----------
AAA  A01           3

You can replace the constant 3 in the query by 2 to find packs that have at least 2 ingredients in common with items:

SQL> SELECT p.pack, i.item, COUNT(*)
  2    FROM pack p
  3    JOIN item i ON p.ingredient = i.ingredient
  4   GROUP BY p.pack, i.item
  5  HAVING COUNT(*) >= 2;

PACK ITEM   COUNT(*)
---- ---- ----------
BCC  A01           2
AAA  A01           3
AAA  A02           2

If the number of ingredients is unknown, this query will return the exact matches:

SQL> SELECT p.pack, i.item, COUNT(*)
  2    FROM (SELECT pack, ingredient,
  3                 COUNT(*) over (PARTITION BY pack) ingredients#
  4            FROM pack) p
  5    JOIN (SELECT item, ingredient,
  6                 COUNT(*) over (PARTITION BY item) ingredients#
  7            FROM item) i ON p.ingredient = i.ingredient
  8                        AND p.ingredients# = i.ingredients#
  9   GROUP BY p.pack, i.item, i.ingredients#
 10  HAVING COUNT(*) = i.ingredients#;

PACK ITEM   COUNT(*)
---- ---- ----------
AAA  A01           3