SQL query correlated which counts of 'where in' parameter

Question

I have a few shops which have products. I want to to write a query where I put product_id and I get max(id)` offer from this shop. And very important thing - all products must be in each shop. If I want two products and the shop has only one, it isn't correct.

My query what is wrong now:

select  max(o.offer_id) as max_offer_id, o.product_id,   
        shop_id, prod.name  
from offer as o
join product as prod on prod.product_id = o.product_id
     where prod.product_id in ( 1,2 )
          group by o.product_id, shop_id , prod.name 
          order by shop_ida

I would like to get a result like this:

    max_offer_id    product_id  shop_id name
    10  1   1   PROD1
    9   2   1   PROD2
    11  1   2   PROD1
    12  2   2   PROD2

but I get this:

    max_offer_id product_id shop_id name

    10  1   1   PROD1
    9   2   1   PROD2
    11  1   2   PROD1
    12  2   2   PROD2
    1   1   3   PROD1 <-- wrong
    4   1   5   PROD1 <-- wrong
    6   2   6   PROD2 <-- wrong

tables and some data: https://www.db-fiddle.com/f/vyMmeUqmgYHmMirMFzwx2Y/5

'put product_id and i get max(id) offer from this shop'- for product_id = 1 there are 4 shops (1,2,3,5), for product_id = 2 there are 3 shops (1,2,6). Total = 4 + 3 = 7, which is the number of records being received. Please check in terms of what exactly you need. — Pankaj, Jul 18 '22 at 20:48
Shop 1 and 2 have all product(1,2), shop 3, 5, 6 have only one product. I need only shop that have all product from in param — zagor, Jul 18 '22 at 21:33

score 1 · Answer 1 · answered Jul 18 '22 at 23:53

This will need selecting records that match ALL in the list. One such example is here.

using same query can be modified as -

select
max(o.offer_id) as max_offer_id, o.product_id,   t.shop_id, prod.name  from offer as o
join product as prod on prod.product_id = o.product_id
join (select shop_id, count(*)  from offer
where product_id in ( 1,2 )
group by shop_id 
having count(distinct product_id)>=
(select count(*) from product where product_id in ( 1,2 ))
) t
on o.shop_id = t.shop_id
where prod.product_id in (1,2)
group by  o.product_id, shop_id , prod.name

Refer fiddle here.

SQL query correlated which counts of 'where in' parameter

1 Answers1