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 !

You need to sign in to view this answers