One to Many request : retrieve only one value of many table depending on condition

I have two tables joined by a Produit_ID field :

Table Products
Produit_ID | Name
1          | Some product
2          | Some other product

Table Prices
Price_ID   | Produit_ID | Code_Prix | Validity_From | Validity_To
1          | 1          | 222       | 2024-01-01    | 2100-01-01
2          | 1          | 658       | 2024-01-01    | 2100-01-01
3          | 2          | 222       | 2024-01-01    | 2100-01-01

The conundrum is as follows : I need to get all fields from both tables but for the Prices table I need only the line containing 658 in Code_Prix if it exists else revert to the 222 Code Prix.

In other words, a request for both products should return the data from Product_ID = 1 and Price_ID = 2 for the first row and the data from Product_ID = 2 and Price_ID = 3 for the second row.

Performance is paramount here. It is the condition requiring me to get this data in a single request since the final query will fetch about 500 products/prices in a tables containing 15k/120k lines.

Can't seem to figure this one.

Any hint appreciated !

Solution

Use two joins, one for each Code_Prix. Use LEFT JOIN for Code_Prix = 658 since it might not exist, then you can use COALESCE() to prefer it, falling back on the row for Code_Prix = 222.

SELECT products.*,
    COALESCE(p1.Price_ID, p2.Price_ID) AS Price_ID,
    COALESCE(p1.Code_Prix, p2.Code_Prix) AS Code_Prix,
    COALESCE(p1.Validity_From, p2.Valdity_From) AS Validity_From,
    COALESCE(p1.Validity_To, p2.Valdity_To) AS Validity_To
FROM products
LEFT JOIN prices AS p1 ON p1.Produit_ID = products.Produit_ID AND p1.Code_Prix = 658
INNER JOIN prices AS p2 ON p2.Produit_ID = products.Produit_ID AND p2.Code_Prix = 222