I have a table products which have an association with product_prices. Each product can have multiple prices for different countries.

Desired outcome: When I run the query, I want to receive all products with 1 single price based on country condition.

Logic

1. If product has price for the specified country-> then country price will be displayed.
2. If product does not have price for the specified country-> then price for with country_id 400 needs to be displayed.
3. If product has no price for specified country and for country with id 400 -> then the price with country_id 500 needs to be displayed.
4. If product has not price for specified country and id 400, and id 500 -> then price which has country with biggest amount of total users will be displayed.

I did something like this for 1 single product. But I don't know how to achieve these for all products.

select p.id, pp.price, pp.country_id,
(CASE WHEN (pp.country_id=:countryId) then 0 else
    (CASE WHEN (pp.country_id=400) then 1 else
        (case when (pp.country_id=500) then 2 else 3 END) END) END) as priorityIndex
from products p
inner join product_prices pp on p.id = pp.product_id
inner join countries c on pp.country_id = c.id
where p.id = '00057c218b154d5b838b928a0189ff9f'
order by priorityIndex limit 1;

My data: Country table

Product price table

My expected output when inserting country 27:

CodePudding user response：

With MySQL 8.0 ROW_NUMBER() allows to drop all rows except first per product:

SELECT * FROM (
    SELECT p.id, 
        pp.price,
        pp.country_id,
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY FIELD(pp.country_id, 500, 400, 27) DESC) AS `rn`
    FROM products AS p
        JOIN product_prices AS pp
            ON p.id = pp.product_id
    WHERE p.id IN (...)
) AS t WHERE t.rn=1

CodePudding user response：

I did something like this. But I am not sure only about the last part. In case product price needs to be choosed based on total dealers.

select A.id, A.price_id, A.price, A.country from
(select p.id, pp.id as price_id, pp.price, pp.country_id as country,
        ROW_NUMBER() over (PARTITION BY p.id order by (CASE WHEN (pp.country_id=:countryId) then 0 else
            (CASE WHEN (pp.country_id=400) then 1 else
                (case when (pp.country_id=500) then 2 else 3 END) END) END), c.total_dealer_users desc) AS rowNumber
 from products p
          inner join product_prices pp on p.id = pp.product_id
          inner join countries c on pp.country_id = c.id
 where p.id in ('00057c218b154d5b838b928a0189ff9f','054a8caf911e4ff3a594990af20a9611')) as A
where rowNumber=1;