Extracting the id of the products missing from the third table with the specified value in SQL

I have 3 tables:

products (id, category, …),

store_offers (product_id, offer_id, platform) – joining the products table with many tables with offers, e.g. ebay, amazon, etc.

ebay_offers (id, status, country, …).

I need to get the id of products (in addition with a specific category_id) that have no listings on ebay (ie not available in the ebay_offers table with the value “uk” for the country column).

I tried something like this unfortunately it doesn’t show me the correct values:

SELECT products.id
    FROM products
    LEFT JOIN store_offers ON store_offers.product_id = products.id
    LEFT JOIN ebay_offers ON ebay_offers.id = store_offers.offer_id
    where products.category_id = 43567 AND store_offers.platform = 'ebay' AND ebay_offers.country = "uk" IS NULL
    ;

I can guess why the query does not generate results, but in this case I have no idea how to extract the data that interests me.

Answer

I recommend using not exists:

select p.id
from products p
where not exists (select 1
                  from store_offers so join 
                       ebay_offers eo
                       on eo.id = so.offer_id
                  where so.product_id = p.id and
                        so.platform = 'ebay' and
                        eo.country = 'uk'
                 ) and
      p.category_id = 43567; 

You can do this with left join. The issue is the country condition in the where clause. It requires a match — conflicting with the null comparison. The syntax for the NULL comparison is also off. So:

SELECT p.id
FROM products JOIN
     store_offers so
     ON so.product_id = p.id AND
        so.platform = 'ebay' LEFT JOIN
     ebay_offers eo
     ON eo.id = so.offer_id AND
        eo.country = 'uk'
WHERE p.category_id = 43567 AND eo.id IS NULL;