Matching multiple columns based on groupID from another table’s data

I have 2 tables, one where I have sets of information that is organized by group ID’s. This group ID means all parts must be in that group for that machine to be an option:

Machine	Part Group 1	Part Group 2	GroupID
Mach A	Part M	Part A	1
Mach A	Part M	Part B	1
Mach A	Part C	Part D	1
Mach B	Part M	Part F	2
Mach C	Part L	Part A	3
Mach C	Part L	Part F	3
Mach A	Part L	Part N	4

The second table is a BOM for each Product:

Product	Part
Prod A	Part A
Prod A	Part F
Prod A	Part M
Prod B	Part B
Prod B	Part M
Prod B	Part X

I’m trying to find the Machines where all distinct values in Part Group 1 and Part Group 2 grouped by group ID are in the 2nd Table Part column, and then spits out all distinct machine names from the first table

Output I am expecting is something like this (if I use Prod A as a reference in this example):

Machine	GroupID
Mach B	2

I have made an attempt at this with something like this, but to be completely honest I’m a bit lost with how to approach this problem:

SELECT [Machine], [GroupID] 
FROM [Table 1] 
WHERE [Part Group 1] IN (
    SELECT [Part] 
    FROM [Table 2] 
    WHERE [Product] = 'Prod A') 
AND [Part Group 2] IN (
    SELECT [Part] 
    FROM [Table 2] 
    WHERE [Product] = 'Prod A') 
GROUP BY [GroupID], [Machine]

Any help appreciated!

Answer

You may follow these steps:

• aggregate all parts for each “Product” into a single string
• extract all machines whose both part groups are found within the product parts
• exclude all machines which have at least one part groups not found within the product parts, for each product.

WITH cte_products AS (
    SELECT Product, STRING_AGG(Part, ',') AS Parts 
    FROM products 
    GROUP BY Product
), cte_machines AS (
    SELECT Product, Machine, GroupID
    FROM      machines 
    LEFT JOIN cte_products
           ON cte_products.Parts LIKE CONCAT('%', machines.PartGroup1, '%') 
          AND cte_products.Parts LIKE CONCAT('%', machines.PartGroup2, '%')
)
SELECT Product, Machine, GroupID
FROM cte_machines
WHERE GroupID NOT IN (SELECT GroupID FROM cte_machines WHERE Product IS NULL)

Check the demo here.