MySQL or JPQL query for getting different values from OneToMany relation (Condition priority)

Question

I have a table products which contains information like

id	product_name	retailer_name
1	Shirt	Gucci

This table has a OneToMany connection with product_prices which contains information like

id	price	country	product_id
100	95.5	United Kingdom	1
101	97.5	France	1
300	70.9	EUROPEAN_UNION	1

I need to create an MySQL or JPQL query which will retrieve

1. product_name
2. retailer_name
3. price (according to country or else choose European Union)

So for example: In case country United Kingdom is chosen -> then it will retrieve:

1. Shirt Gucci 95.5

In case country Italy is chosen -> then it will retrieve:

1. Shirt Gucci 70.9

As you can remark, in the second example, system retrieved EUROPEAN_UNION price because Italy could not be found.

In case country EUROPEAN_UNION is chosen -> then it will retrieve:

1. Shirt Gucci 70.9

As you can remark, in the third example, system retrieved EUROPEAN_UNION price because EUROPEAN_UNION country was found.

How can I achieve that ?

I have in tried in JPQL something like :

select new com.ProductDTOWithPrice(p.id, p.product_name, p.retailer_name, " +
        "pr.price) " +
        "from Product p " +
        "join ProductPrice pr " +
        "where p.id =:productId and (case when pr.country=:country then 1 " +
        "else when pr.country.id = EUROPEAN_UNION then 1 end)"

Also, tried to make multiple selects or use exists, but still, I can not figure it out. I am getting stuck on retrieving ''price''.

    select p.id, p.name,
       Max(IF(pp.country_id = :countryId, true, false)) as mainCountry,
       Max(IF(pp.country_id = 400, true, false)) as europeanCountry
from products p
         join product_prices pp on p.id = pp.product_id
where p.id = '0002fb8977754949bf4da70535e1a2e6'
GROUP BY p.id;

Any help please ?

Answer 1

I have solved the problem using MYSQL :

SELECT p.id, 
       p.name, 
       pp.price, 
       country, 
       (CASE WHEN (pp.country_id = :country      ) THEN 3
             WHEN (pp.country_id = EUROPEAN_UNION) THEN 2                                        
             ELSE 0 END                                  ) AS priorityIndex
FROM       products p
INNER JOIN product_prices pp 
        ON p.id = pp.product_id
WHERE p.id  = '0002fb8977754949bf4da70535e1a2e6'
ORDER BY priorityIndex DESC
LIMIT 1;

If anyone knows a better solution, please let me know.