CODEWITHSUNDEEP
postgresql
piofusco
piofusco

Reputation: 239

SQL - finding multiple occurrence of one attribute with another in same table

Consider the following tables

Inventory(storeid, itemid, qty)
Items(itemid, description, size, color)

Here is my task: retrieve the id of stores that meet the following criterion: for every item description that is held in its inventory, the store holds a corresponding itemID in all possible sizes for that description.

This is how the response should look: 

3667
3706
3742
3842

Where I am at:

with s as (
    select * 
    from inventory 
    inner join items using (itemID) 
),
m as (
    select count(distinct size), description
    from items
    group by description
),
sizes as (
    select distinct size
    from items
)
select distinct s1.storeID
from s s1 
    inner join m m1
    on s1.description = m1.description
    group by s1.storeID;

This just returns storeid's with items that match any of the descriptions...which is every storeid. Having trouble finding a way to grab a description and ensure it has all three sizes (small, medium, large).

http://sqlfiddle.com/#!2/2a743e

Upvotes: 1

Views: 475

Answers (2)

Jakub Kania
Jakub Kania

Reputation: 16467

It doesn't say three sizes but all possible sizes and I like arrays so:

WITH sizes AS (
    SELECT description, array_agg(DISTINCT size) AS sizes
    FROM items
    GROUP BY description
)
,store_items AS(
    SELECT s.storeID, it.description, array_agg(DISTINCT it.size) AS sizes
    FROM stores AS s
      JOIN inventory AS i 
        ON s.storeID = i.storeID
      JOIN items AS it 
        ON i.itemID = it.itemID
     GROUP BY s.storeID, it.description
)
SELECT s.storeID
FROM stores AS s
WHERE s.storeID NOT IN(
  SELECT storeID
  FROM store_items AS si     
  JOIN sizes z
    ON z.description = si.description
    AND si.sizes<>z.sizes)

fiddle

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

Using the having clause we can find the descriptions that have a count of size = 3. We then count the number of those descriptions and compare that to the total number of descriptions in the store.

WITH countofdescirptions 
     AS (SELECT i.storeid, 
                Count(DISTINCT it.description)K 
         FROM   inventory i 
                INNER JOIN items it 
                        ON i.itemid = it.itemid 
         GROUP  BY i.storeid), 
     has3  
     AS (SELECT i.storeid, 
                it.description 
         FROM   inventory i 
                INNER JOIN items it 
                        ON i.itemid = it.itemid 
         GROUP  BY i.storeid, 
                   it.description 
         HAVING Count(DISTINCT size) = 3) 
SELECT * 
FROM   countofdescirptions 
       INNER JOIN (SELECT storeid, 
                          Count(description)K 
                   FROM   has3
                   GROUP BY storeid) has3Count 
               ON countofdescirptions.storeid = has3Count.storeid 
                  AND countofdescirptions.k = has3Count.k

DEMO

I'm fairly certain that there's a solution using COUNT() OVER

Upvotes: 1

Related Questions