Get exact match between two table

Question

I have a issue that is hard to explain. I have two tables

table1: (this is something like shipping table)

ID  ShippingId ProductId1 ProductId2 
1   100        A          A1
2   100        A          A2
3   100        A          A3
4   100        A          A4
5   100        A          A5

6   200        B          B1
7   200        B          B2

8   300        B          A1
9   300        B          A2

table2: (and this is about relation between ProductId1 and ProductId2)

ID  ProductId1 ProductId2 
1   A          A1
2   A          A2
3   A          A3
4   A          A4
5   A          A5
6   B          B1
7   B          B2

In the case the shipment "100" includes all items of "A" so this should "true" and the shipments "200" and "300" does not include all parts of their main products. So expected output should be like

ShippingId ProductId1 IsIncludeAll
100        A          true
200        B          false
300        A          true

can you guys help me?

DECLARE @table1 AS TABLE
(
    ShippingID INT,
    ProductId1 INT,
    ProductId2 INT
)
DECLARE @table2 AS TABLE
(
    ProductId1 INT,
    ProductId2 INT
)

INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (100,111, 1119)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (100,111, 1118)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (100,111, 1117)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (100,111, 1116)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (100,111, 1115)

INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (200,222, 2229)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (200,222, 2228)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (300,111, 1117)
INSERT INTO @table1 (ShippingId,ProductId1,ProductId2) VALUES (300,111, 1116)


INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 111, 1119)
INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 111, 1118)
INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 111, 1117)
INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 111, 1116)
INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 111, 1115)

INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 222, 2229)
INSERT INTO @table2 (ProductId1,ProductId2) VALUES ( 222, 2228)



ShippingId ProductId1 IsIncludeAll
100        A          true
200        B          false
300        A          false

Answer 1

Little confused on your sample data and output. From my thought Check this query and output.

------Step 1. concatenate product1 and product2 with ShippingID wise,product1 wise order by ShippingID,ProductId1,ProductId2-------------------
declare @Shipping as table
(
    ShippingID INT,
    ProductId1 INT,
    ProductDesc varchar(max)
)
insert @Shipping
SELECT Shipping.ShippingID,Shipping.ProductId1,
       LEFT(Shipping.prod_desc,Len(Shipping.prod_desc)-1) As prod_desc 
FROM
    (
        SELECT DISTINCT T2.ShippingID, T2.ProductId1,
            (
                SELECT cast(T1.ProductId1 as varchar(10))+'-' +cast(T1.ProductId2 as varchar(10))+ '|' AS [text()]
                FROM dbo.table1 T1
                WHERE T1.ShippingID = T2.ShippingID and T1.ProductId1=T2.ProductId1 
                ORDER BY T1.ShippingID,ProductId1,ProductId2
                FOR XML PATH ('')
            ) prod_desc
        FROM dbo.table1 T2
    ) Shipping

------Step 2. concatenate product1 and product2 with product1 wise order by ProductId1,ProductId2-------------------    
declare @relation as table
(
    ProductId1 INT,
    ProductDesc varchar(max)
) 
insert @relation
SELECT   relation.ProductId1,LEFT(relation.prod_desc,Len(relation.prod_desc)-1) As prod_desc 
FROM
    (
        SELECT DISTINCT T2.ProductId1, 
            (
                SELECT cast(T1.ProductId1 as varchar(10))+'-' +cast(T1.ProductId2 as varchar(10))+ '|' AS [text()]
                FROM dbo.table2 T1
                WHERE T1.ProductId1=T2.ProductId1 
                ORDER BY ProductId1,ProductId2
                FOR XML PATH ('')
            ) prod_desc
        FROM dbo.table2 T2
    ) relation


------Step 1. use left join to match with concatinated string of every product1. if matches return True otherwise False-------------------    
select a.ShippingID,a.ProductId1,case when b.ProductDesc is null then 'False' else 'True' end as IsIncludeAll 
from @Shipping a
left join @relation b
on a.ProductId1=b.ProductId1 and a.ProductDesc=b.ProductDesc


-----Result-----------

------------+---------------+--------------
ShippingID  | ProductId1    | IsIncludeAll
------------+---------------+--------------
100         | 111           | True
200         | 222           | True
300         | 111           | False

Answer 2

A total guess, as the sample DDL and DML don't match the sample data, but perhaps this?

SELECT S.ShippingID,
       T2.ProductId1,
       CASE COUNT(CASE WHEN T1.ProductId2 IS NULL THEN 1 END) WHEN 0 THEN 'true' ELSE 'false' END AS IsIncludeAll
FROM @table2 T2
     CROSS APPLY (SELECT DISTINCT
                         sq.ShippingID,
                         sq.ProductId1
                 FROM @table1 sq
                 WHERE sq.ProductId1 = T2.ProductId1) S
     LEFT JOIN @table1 T1 ON T2.ProductId1 = T2.ProductId1
                         AND T1.ProductId2 = T2.ProductId2
                         AND S.ShippingID = T1.ShippingID
GROUP BY S.ShippingID,
         T2.ProductId1;