SQL N To No Relationship Table

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

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

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