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:

The second table is a BOM for each Product:

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):

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!

CodePudding user response：

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.

CodePudding user response：

Here is what I'm thinking...

with
    parts as (
        select [Machine], [GroupID], [Part Group 1] as [Part] from [Table 1]
        union
        select [Machine], [GroupID], [Part Group 2] as [Part] from [Table 1]
    ),
    parts_not_used as (
        select [Machine], [GroupID], [Part]
        from parts
        where [Part] not in (select [Part] from [Table 2])
    )
select distinct
    [Machine], [GroupID]
from
    parts 
where
    not exists(
        select 1 from parts_not_used
        where parts_not_used.Machine = parts.Machine
        and parts_not_used.GroupID = parts.GroupID
    )

First make a set of all the Parts for a Machine and GroupID. Then find all the of those Parts which are NOT used in the making of any Product. Then find the distinct Machine, GroupID pairs that have no unused Parts.