SQL query that finds the an id that is not associated with another id

user_13019749 0
Viewed 216

I'm currently learning the ropes of SQL and i have an tutorial from school that goes like this: All stores (storeid) sells (productid, storeid) some products (productid)   A store is considered a monopoly if every product they sell is not sold by any other store. How do I find the monopolies? I was thinking of selecting the storeid from 2 of the same tables, but I'm not sure how to continue from there on.

Tables are below:

Store:

+-----------+
|  storeid  |
+-----------+
| --------- |
|     1     |
|     2     |
|     3     |
|     4     |
|     5     |
+-----------+

Products:

+-------------+
|  productid  |
+-------------+
| ---------   |
|     1       |
|     2       |
|     3       |
|     4       |
|     5       |
|     6       |
+-------------+

Sells:

+--------------------------+
|   productid | storeid    |
+--------------------------+
| -----------+------------ |
|          1 |          1  |
|          2 |          1  |
|          2 |          2  |
|          3 |          2  |
|          1 |          2  |
|          3 |          3  |
|          2 |          4  |
|          4 |          4  |
|          5 |          5  |
|          6 |          5  |
+--------------------------+

So by my count, only store 5 is considered a monopoly, because they sell products that are not available in other stores.

postgresql mysql sql
2 Answers

We can try a self join approach combined with aggregation:

SELECT t1.storeid
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.productid = t1.productid AND
       t2.store_id <> t1.storeid
GROUP BY t1.storeid
HAVING COUNT(t2.storeid) = 0;

The approach here is to try to match each row in Sells to some other row on the condition that it is the same product, but is being sold by some other store. A matching store is one for which none of its products are being sold by other stores, so the count of the second table column in the join should be zero.

user_18632290

Use window functions and aggregation:

select s.storeid
from (select s.*,
             count(*) over (partition by productid) as num_stores
      from sells s
     ) s
group by s.storeid
having max(num_stores) = 1;

This should be much faster than a self-join. It is also almost a direct translation of your question. The subquery counts the number of stores where each product is sold. The outer query selects stores where all products are sold in one store.

user_11440350
Related