SQL N To No Releationship Table-CodePudding

I have 3 table by this names

Supplier :For store supplier info

SupplierID  Name
1   Supplier 1
2   Supplier 2
3   Supplier 3
4   Supplier 4

Product : For store product info

ProductID   Name
1   Product 1
2   Product 2
3   Product 3
4   Product 4
5   Product 5

SpplierProduct : For store Product that supplier can supply

ProductID   SupplierID
2   1
3   1
4   1
2   2
3   2
4   2
3   3
4   3
1   4
2   4
4   4

I want to write a query that get a bunch of product ID and return the supplier ID that have all this product ID (N:N Relation) for example get product ID 2,3 and return just supplier ID 1,2

CodePudding user response：

You can use intersect as follows:

select distinct SupplierID 
from SupplierProduct
where ProductID = 2
intersect
select SupplierID 
from SupplierProduct
where ProductID = 3

Fiddle

CodePudding user response：

This is a question of Relational Division With Remainder, with multiple divisors.

Firstly, to be able to make good solutions for this, you need your input data in tabular form. You can use a table variable or a Table Valued Parameter for this.

There are many solutions. Here is one common one:

Join the input data to the SupplierProduct table. In your case, you only want the Supplier data, so do this in a subquery.
Group it up and check that the count matches the total count of inputs

DECLARE @ProductInput TABLE (ProductID int);
INSERT @ProductInput (ProductID) VALUES (2),(3);

SELECT *
FROM Supplier s
WHERE (SELECT COUNT(*)
    FROM SupplierProduct sp
    JOIN @ProductInput pi ON pi.ProductID = sp.ProductID
    WHERE sp.SupplierID = s.SupplierID
) = (SELECT COUNT(*) FROM @ProductInput)
;

db<>fiddle

Another common solution is a double NOT EXISTS. This verifies that there are no inputs which do not have a match. It is generally considered to be less efficient.

DECLARE @ProductInput TABLE (ProductID int);
INSERT @ProductInput (ProductID) VALUES (2),(3);

SELECT *
FROM Supplier s
WHERE NOT EXISTS (SELECT 1
    FROM @ProductInput pi
    WHERE NOT EXISTS (SELECT 1
        FROM SupplierProduct sp
        WHERE pi.ProductID = sp.ProductID
          AND sp.SupplierID = s.SupplierID
    )
);