SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE machines(
    Machine 	VARCHAR(30),
    PartGroup1  VARCHAR(30),
    PartGroup2 	VARCHAR(30),
    GroupID     INT
);
INSERT INTO machines VALUES
('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);

SELECT * FROM machines;


Machine
PartGroup1
PartGroup2
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


CREATE TABLE products(
    Product    VARCHAR(30), 	
    Part       VARCHAR(30)
);
INSERT INTO products VALUES
('Prod A', 	'Part A'),
('Prod A', 	'Part F'),
('Prod A', 	'Part M'),
('Prod B', 	'Part B'),
('Prod B', 	'Part M'),
('Prod B', 	'Part X');

SELECT * FROM products;


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


SELECT Product, STRING_AGG(Part, ',') AS Parts 
FROM products 
GROUP BY Product


Product
Parts


Prod A
Part A,Part F,Part M

Prod B
Part B,Part M,Part X


WITH cte_products AS (
    SELECT Product, STRING_AGG(Part, ',') AS Parts 
    FROM products 
    GROUP BY Product
)
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, '%')


Product
Machine
GroupID


Prod A
Mach A
1

Prod B
Mach A
1

null
Mach A
1

Prod A
Mach B
2

null
Mach C
3

null
Mach C
3

null
Mach A
4


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)


Product
Machine
GroupID


Prod A
Mach B
2

Machine	PartGroup1	PartGroup2	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

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