MS SQL Server - Left join with group by in both left and right table-CodePudding

I have two tables in MS SQL Server.

Table A contains a list of Customer IDs, and the products they have purchased.

Products are bundled together to form a package.

Table B contains the groups of products, and the packages that they form.

Example:

Table A:

CustID     Product
-------------------
10001      Mobo_1
10001      GPU_1
10002      Mobo_1
10002      GPU_2

Table B:

Product    Package
-------------------
Mobo_1     Pack_A
GPU_1      Pack_A
Mobo_1     Pack_B
GPU_2      Pack_B

I would like to map the Package column from Table B to Table A, taking into consideration the specific combination of products that a specific customer purchased,

i.e. Mobo_1 GPU_1 should map to Pack_A, and Mobo_1 GPU_2 should map to Pack_B.

Resulting output:

CustID     Product     Package
-------------------------------
10001      Mobo_1      Pack_A
10001      GPU_1       Pack_A
10002      Mobo_1      Pack_B
10002      GPU_2       Pack_B

I have tried searching for LEFT JOIN GROUP BY WHERE conditions, but could not seem to find any similar questions or solutions.

CodePudding user response：

Yes it can be done, but it takes some effort

select a.CustID,
       a.Product,
       t3.Package
from   TableA a
  left join ( select t.CustID,
                     t2.Package
              from   ( select  a.CustID,
                               ( select top 1 string_agg(a.Product, ' ')
                                 from   TableA a2
                                 where  a2.CustID = a.CustID
                               ) as Products
                       from    TableA a
                       group by a.CustID
                     ) t   
                left join ( select b.Package,
                                   ( select top 1 string_agg(b.Product, ' ')
                                     from   TableB b2
                                     where  b2.Package = b.Package
                                   ) as Products
                            from   TableB b
                            group by b.Package
                          ) t2 on t.Products = t2.Products
            ) t3 on a.CustID = t3.CustID

the result is

CustID  Product Package
10001   Mobo_1  Pack_A
10001   GPU_1   Pack_A
10002   Mobo_1  Pack_B
10002   GPU_2   Pack_B

You can try it yourself at this DBFiddle

EDIT

The trick I used is to combine the products into one varchar ('Mobo_1 GPI_1' and 'Mobo_1 GPI_2') for both tables, and then you can use those varchar's to join.
It will be more clear when you look at the last queries in the DBFiddle