SQL N To No Releationship Table

Question

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

Please read https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions Post the code you have so far, your desired outcome as a text table based on your sample data and state any difficulties you are having. — P.Salmon, Feb 14 '22 at 08:47

score 0 · Answer 1 · answered Feb 14 '22 at 08:51

0

You can use intersect as follows:

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

Fiddle

answered Feb 14 '22 at 08:51

Zakaria

4,715
2
5
31

Yes but i do'nt know have manay product ID i have so i most use IN. – javad daryabari Feb 14 '22 at 10:19
In my Answer, your Product ID List are in the @ProductSelection Table. By leaving uncommented one of the ANDs in the WHERE clause, you can choose one of the 3 sets of ProductsID i put there as examples. Did you checked my answer? – Andy3B Feb 15 '22 at 18:44

score 0 · Accepted Answer · answered Feb 14 '22 at 10:37

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

Andy3B · Answer 3 · 2022-02-14T20:27:33.917

Try this:

DECLARE     @Supplier TABLE (SupplierID int, Name varchar(50));
INSERT INTO @Supplier VALUES
        (1, 'Supplier 1')
    ,   (2, 'Supplier 2')
    ,   (3, 'Supplier 3')
    ,   (4, 'Supplier 4')
;
DECLARE     @Product TABLE (ProductID int, Name varchar(50));
INSERT INTO @Product VALUES
        (1, 'Product 1')
    ,   (2, 'Product 2')
    ,   (3, 'Product 3')
    ,   (4, 'Product 4')
    ,   (5, 'Product 5')
;

DECLARE     @SupplierProduct TABLE (ProductID int, SupplierID int);
INSERT INTO @SupplierProduct VALUES
        (2, 1)
    ,   (3, 1)
    ,   (4, 1)
    ,   (2, 2)
    ,   (3, 2)
    ,   (4, 2)
    ,   (3, 3)
    ,   (4, 3)
    ,   (1, 4)
    ,   (2, 4)
    ,   (4, 4)
;

DECLARE     @ProductSelection   TABLE   (ProductID int)
INSERT INTO @ProductSelection
    SELECT
        ProductID
    FROM @Product
    WHERE   1=1
        --  AND ProductID IN (2, 3) --   returns Suppliers 1, 2
        --  AND ProductID IN (3, 4) --   returns Suppliers 1, 2, 3
            AND ProductID IN (2, 4) --   returns Suppliers 1, 2, 4
;

WITH SupplierList AS
    (
        SELECT
                RowNo = ROW_NUMBER() OVER (PARTITION BY SP.SupplierID ORDER BY SP.SupplierID)
            ,   S.SupplierID
        FROM    @SupplierProduct    SP
        JOIN    @ProductSelection   P   ON  P.ProductID     = SP.ProductID
        JOIN    @Supplier           S   ON  S.SupplierID    = SP.SupplierID
    )

SELECT  
        SupplierID
FROM    SupplierList
WHERE   RowNo   = (SELECT SUM(1) FROM @ProductSelection)

SQL N To No Releationship Table

3 Answers3