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