CODEWITHSUNDEEP
sqlsql-server
Ram Grandhi
Ram Grandhi

Reputation: 409

Products not available in each store

I have a list of tables

Product

{  ProdID (INT),  Name(varchar),   ...}

Store

{ StoreID(INT), Location(varchar), ...}

Inventory

 {StoreID(INT) references Store(StoreID) ,ProdID(INT) references Products(ProdID),...}

I want the list of products not available in each store

Upvotes: 0

Views: 71

Answers (3)

Beth
Beth

Reputation: 9617

try this:

SELECT 
    productID, count_storeID
FROM
    (SELECT
        productid,
        COUNT(DISTINCT storeid) AS count_storeID
    FROM
        inventory
    GROUP BY 
        productID) i FULL OUTER JOIN
    (SELECT COUNT(*) AS count_stores FROM storeID   ) s
WHERE
    count_storeID < count_stores

Upvotes: 0

ErstwhileIII
ErstwhileIII

Reputation: 4863

Then you want to select all stores that do not have products in inventory.

select s.StoreID, p.ProdID, p.Name
  from Store s, (select ProdID, Name 
                   from Product
                  where ProdID not in (select i.ProdID 
                                         from inventory i
                                        where i.StoreID = s.StoreID
                                      )
                ) p
 order by s.StoreID, p.Name

Upvotes: 0

M.Ali
M.Ali

Reputation: 69564

;WITH CTE
AS (
    SELECT P.ProdID 
         , S.StoreID
    FROM Product P CROSS JOIN Store S
   )
SELECT C.StoreID, C.ProdID
FROM CTE C
WHERE NOT EXISTS (SELECT 1
                  FROM Inventory
                  WHERE C.StoreID = StoreID 
                   AND  C.ProdID  = ProdID)

Upvotes: 1

Related Questions